时间戳

首页 > 解决方案 > 具有不同日期的列到行

sql - 具有不同日期的列到行

问题描述

请参阅下面的示例数据和所需的输出格式：

--SAMPLE TABLE
DECLARE @TEMP TABLE(
    DATA_DATE DATE,
    PROD_ID INT,
    CAT_CODE NVARCHAR(10),
    DATABUCKET_1 FLOAT,
    DATABUCKET_2 FLOAT,
    DATABUCKET_3 FLOAT,
    DATABUCKET_4 FLOAT,
    DATABUCKET_5 FLOAT);

INSERT INTO @TEMP VALUES('19-Oct-2018',100,'C1', 100,200,300,400,500)

SELECT * FROM @TEMP;

--PREFERRED OUTPUT FORMAT

SELECT 'C1' AS CAT_CODE, '19-Oct-2018' AS DATA_DATE, 100 AS UNITS, 'W1' AS WEEK_NUM--FOR DATABUCKET_1, THE DATE REMAINS SAME (AS DATA_DATE)
UNION ALL
SELECT 'C1' AS CAT_CODE, '12-Oct-2018' AS DATA_DATE, 200 AS UNITS, 'W2' AS WEEK_NUM--FOR DATABUCKET_2, THE DATE IS ONE WEEK BEFORE THAT OF W1
UNION ALL
SELECT 'C1' AS CAT_CODE, '05-Oct-2018' AS DATA_DATE, 300 AS UNITS, 'W3' AS WEEK_NUM--FOR DATABUCKET_3, THE DATE IS ONE WEEK BEFORE THAT OF W2
UNION ALL
SELECT 'C1' AS CAT_CODE, '28-Sep-2018' AS DATA_DATE, 400 AS UNITS, 'W4' AS WEEK_NUM--FOR DATABUCKET_4, THE DATE IS ONE WEEK BEFORE THAT OF W3
UNION ALL
SELECT 'C1' AS CAT_CODE, '21-Sep-2018' AS DATA_DATE, 500 AS UNITS, 'W5' AS WEEK_NUM--FOR DATABUCKET_5, THE DATE IS ONE WEEK BEFORE THAT OF W4

补充几点：

请让我知道如何使用 UNPIVOT 实现这一目标。提前致谢

标签： sqlsql-servertsqltransposeunpivot

解决方案

你可以un-pivot使用CROSS APPLY

SELECT  t.CAT_CODE, d.*
FROM    @TEMP t
    CROSS APPLY
    (
        SELECT  DATA_DATE = t.DATA_DATE, UNITS = t.DATABUCKET_1, WEEK_NUM = 'W1'    union all
        SELECT  DATA_DATE = DATEADD(DAY, -7, t.DATA_DATE), UNITS = t.DATABUCKET_2, WEEK_NUM = 'W2'  union all
        SELECT  DATA_DATE = DATEADD(DAY, -14, t.DATA_DATE), UNITS = t.DATABUCKET_3, WEEK_NUM = 'W3' union all
        SELECT  DATA_DATE = DATEADD(DAY, -21, t.DATA_DATE), UNITS = t.DATABUCKET_4, WEEK_NUM = 'W4' union all
        SELECT  DATA_DATE = DATEADD(DAY, -28, t.DATA_DATE), UNITS = t.DATABUCKET_5, WEEK_NUM = 'W5'
    ) d

或使用计数/数字表

SELECT  t.CAT_CODE, DATA_DATE = DATEADD(DAY, -7 * n, t.DATA_DATE),
    UNITS   = CASE n
            WHEN 0 THEN t.DATABUCKET_1
            WHEN 1 THEN t.DATABUCKET_2
            WHEN 2 THEN t.DATABUCKET_3
            WHEN 3 THEN t.DATABUCKET_4
            WHEN 4 THEN t.DATABUCKET_5
            END,
    WEEK_NUM = 'W' + CONVERT(VARCHAR(10), n + 1)
FROM    @TEMP t
    INNER JOIN NUMBERS n    ON  n   between 0 and 4

如果你真的有 106 个桶，你真的应该考虑标准化你的表。否则，您需要对 106 行重复上述操作。另一种方法是使用Dynamic SQL来处理

推荐阅读