格式化列使用UNPIVOT

Question

时，我有一个表具有以下结构

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

我想他们UNPIVOT这一格式

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

我可以使用下面的语句抢出使用UNPIVOT数据

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 

事情是 - 我想避免在这里有一个嵌套的选择 - 一个选择为unpiv另一个选择将其格式化为文本中的日期时间。

有没有办法在一个单一的select语句中合并它们？

Answer 1

没关系 - 它了。我喝了什么，我没有马上看到它？ ....DATETIMEFROMPARTS(C.YEAR,C.MONTH, SUBSTRING(C.REQUIRED_DATE,8,2),0,0,0,0)...... –

Answer 2

尝试以下查询：

SELECT up.*, 
     DATEFROMPARTS(up.[YEAR], up.[MONTH], up.[DAY]) AS SaleDate, -- SQL2012+ 
     DATEADD(DAY, up.[DAY] - 1, DATEADD(MONTH, up.[MONTH] - 1, DATEADD(YEAR, up.[YEAR]-1900, {d '1900-01-01'}))) AS SaleDate2, -- SQL2005+ 
     CONVERT(SMALLDATETIME, LTRIM(up.[YEAR]) + RIGHT('0' + LTRIM(up.[MONTH]), 2) + RIGHT('0' + LTRIM(up.[DAY]), 2)) AS SalesDate3 -- SQL2005+ 
FROM (VALUES 
    (2015, 7, 'AAA', 100, 200, 300, 400), 
    (2015, 8, 'BBB', 500, NULL, NULL, NULL) 
) ms([YEAR], [MONTH], ITEM, REQ_1,REQ_2,REQ_3,REQ_4) -- MonthlySales 
CROSS APPLY (
                 -- Remove WHERE filters to get all days (including those without sales) 
    SELECT ms.ITEM, ms.[YEAR], ms.[MONTH], 1, ms.REQ_1 WHERE ms.REQ_1 IS NOT NULL 
    UNION ALL 
    SELECT ms.ITEM, ms.[YEAR], ms.[MONTH], 2, ms.REQ_2 WHERE ms.REQ_2 IS NOT NULL 
    UNION ALL 
    SELECT ms.ITEM, ms.[YEAR], ms.[MONTH], 3, ms.REQ_3 WHERE ms.REQ_3 IS NOT NULL 
    UNION ALL 
    SELECT ms.ITEM, ms.[YEAR], ms.[MONTH], 4, ms.REQ_4 WHERE ms.REQ_4 IS NOT NULL 
    -- UNION ALL ... other months 
) up(ITEM, [YEAR], [MONTH], [DAY], REQ) -- Unpivot 

输出：

/* 
ITEM YEAR  MONTH  DAY   REQ   SaleDate SaleDate2 SalesDate3 
---- ----------- ----------- ----------- ----------- ---------- ---------- ---------- 
AAA 2015  7   1   100   2015-07-01 2015-07-01 2015-07-01 
AAA 2015  7   2   200   2015-07-02 2015-07-02 2015-07-02 
AAA 2015  7   3   300   2015-07-03 2015-07-03 2015-07-03 
AAA 2015  7   4   400   2015-07-04 2015-07-04 2015-07-04 
BBB 2015  8   1   500   2015-08-01 2015-08-01 2015-08-01 
*/