SQL pivot table

[jodjim]

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 will the 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?

Would greatly appreciate your help.

Thanks.

jodjim

 

[jodjim]

After getting ideas from different sources in internet, I finally came up with the following which is working for me. Just in case it will help others too.

INSERT INTO tblTwo
SELECT *
FROM (SELECT RecType, Code1 As CodeOne, Code2 As CodeTwo, Left(YR,4) + '01' AS Period, Per1 AS Amt
FROM tblOne
UNION
SELECT RecType, Code1 As CodeOne, Code2 As CodeTwo, Left(YR,4) + '02' AS Period, Per2
FROM tblOne
UNION
SELECT RecType, Code1 As CodeOne, Code2 As CodeTwo, Left(YR,4) + '03' AS Period, Per3
FROM tblOne
UNION
SELECT RecType, Code1 As CodeOne, Code2 As CodeTwo, Left(YR,4) + '04' AS Period, Per4
FROM tblOne
UNION
SELECT RecType, Code1 As CodeOne, Code2 As CodeTwo, Left(YR,4) + '05' AS Period, Per5
FROM tblOne
UNION
SELECT RecType, Code1 As CodeOne, Code2 As CodeTwo, Left(YR,4) + '06' AS Period, Per6
FROM tblOne
UNION
SELECT RecType, Code1 As CodeOne, Code2 As CodeTwo, Left(YR,4) + '07' AS Period, Per7
FROM tblOne
UNION
SELECT RecType, Code1 As CodeOne, Code2 As CodeTwo, Left(YR,4) + '08' AS Period, Per8
FROM tblOne
UNION
SELECT RecType, Code1 As CodeOne, Code2 As CodeTwo, Left(YR,4) + '09' AS Period, Per9
FROM tblOne
UNION
SELECT RecType, Code1 As CodeOne, Code2 As CodeTwo, Left(YR,4) + '10' AS Period, Per10
FROM tblOne
UNION
SELECT RecType, Code1 As CodeOne, Code2 As CodeTwo, Left(YR,4) + '11' AS Period, Per11
FROM tblOne
UNION
SELECT RecType, Code1 As CodeOne, Code2 As CodeTwo, Left(YR,4) + '12' AS Period, Per12
FROM tblOne) DERIVEDTBL