peter peter - 3 months ago 15
SQL Question

Converting columns into Rows from 2 tables

I have a query which joins 2 tables and the query i have from 2 tables is:

SELECT A.ID,A.CSTID,B.OCT,B.NOV,B.DEC
FROM TBL A
INNER JOIN TBL B
ON A.ID = B.NUM


Output of above query is:

ID CSTID OCT NOV DEC
1 1A 20 25 30


I want the output as:

ID CSTID MONTHS VALUE
1 1A OCT 20
1 1A NOV 25
1 1A DEC 30


I used the below query but i am getting error:

SELECT A.ID,A.CSTID,
C.MONTHS,
C.VALUE
FROM
TBL A
INNER JOIN TBL B
ON A.ID = B.NUM
UNPIVOT (VALUE FOR
MONTHS IN (OCT,NOV,DEC)
)C

vkp vkp
Answer

Wrap the joined result around with select * from ... and use the unpivot.

SELECT * FROM (
SELECT A.ID,A.CSTID,B.OCT as october,B.NOV as november, B.DEC
as december 
 FROM TBL A 
INNER JOIN TBL B ON A.ID = B.NUM ) T
UNPIVOT (
         VALUE FOR MONTHS IN (october,november,december)
        ) C
Comments