I have an SQL table (tblOne) created through DTS with the following columns: RecType, Code1, Code2, Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12. It's the same layout from a source table in a different database.
Now I want to have another table (tblTwo) that will re-arrange the data from tblOne and with columns: RecType, Code1, Code2,MonthNumber, Amount. The Amount will come from the values of Month1 to Month12.
Is it possible to do this with an SQL script? If so, what will it look like (I have a limited knowledge of SQL commands.)? Or, can it be done with the DTS so that tblOne will have the pivot table layout and I don't need to create tblTwo?