Search code examples
sql-servert-sqlunpivot

Formatting columns when using UNPIVOT

I have a table with the following structure

YEAR, MONTH, ITEM, REQ_1,REQ_2,REQ_3,,.....REQ_31
2015,   07,  AAA,   100,   200, 300.......550

I want to unpivot them to this format

ITEM,  Date, Quantity
AAA,01-07-2015,100
AAA,02-07-2015,200
AAA,03-07-2015,300
.
.
AAA,31-07-2015,550

I can grab the data out using UNPIVOT using the following statement

SELECT C.ITEM, CONVERT(DATETIME, (C.YEAR + C.MONTH + SUBSTRING(C.REQUIRED_DAY,8,2)),112) AS REQ_DATE,C.QUANTITY FROM 
(SELECT B.YEAR, B.MONTH, B.ITEM, B.REQUIRED_DAY, B.QUANTITY
FROM C25KPPF A
UNPIVOT
(   
    QUANTITY FOR REQUIRED_DAY IN ([REQ_QTY1], [REQ_QTY2], [REQ_QTY3]....[REQ_QTY31])
) AS B 
WHERE B.QUANTITY <> 0)  C

The thing is - I want to avoid having a nested select here - one select for the unpivot and another select to format it as datetime from the text.

Is there a way to merge this together in one single select statement?

Solution

  • Never mind - figured it out. What was I drinking that I didn't see this straight away? ....DATETIMEFROMPARTS(C.YEAR,C.MONTH, SUBSTRING(C.REQUIRED_DATE,8,2),0,0,0,0)...... –