Sql-Server-2012
想在 SQL 中同時使用 pivot 和 lag 函式
我在一張表中有以下數據。我想把這些數據放在列中，然後根據年份有所不同。
+---------+--------+------+-------+ | Product | REGION | YEAR | Sales | +---------+--------+------+-------+ | TEA | D1 | 2018 | 40 | | TEA | D2 | 2018 | 30 | | TEA | D3 | 2018 | 20 | | TEA | D4 | 2018 | 10 | | TEA | D5 | 2018 | 7 | | COFFEE | D1 | 2018 | 70 | | COFFEE | D2 | 2018 | 100 | | COFFEE | D3 | 2018 | 200 | | COFFEE | D4 | 2018 | 300 | | COFFEE | D5 | 2018 | 400 | | TOFFEE | D1 | 2018 | 30 | | TOFFEE | D2 | 2018 | 7 | | TOFFEE | D3 | 2018 | 400 | | TOFFEE | D4 | 2018 | 34 | | TOFFEE | D5 | 2018 | 90 | | TEA | D1 | 2019 | 1 | | TEA | D2 | 2019 | 2 | | TEA | D3 | 2019 | 3 | | TEA | D4 | 2019 | 4 | | TEA | D5 | 2019 | 5 | | COFFEE | D1 | 2019 | 6 | | COFFEE | D2 | 2019 | 7 | | COFFEE | D3 | 2019 | 8 | | COFFEE | D4 | 2019 | 9 | | COFFEE | D5 | 2019 | 10 | | TOFFEE | D1 | 2019 | 11 | | TOFFEE | D2 | 2019 | 12 | | TOFFEE | D3 | 2019 | 13 | | TOFFEE | D4 | 2019 | 14 | | TOFFEE | D5 | 2019 | 15 | | TEA | D1 | 2020 | 16 | | TEA | D2 | 2020 | 17 | | TEA | D3 | 2020 | 18 | | TEA | D4 | 2020 | 19 | | TEA | D5 | 2020 | 20 | | COFFEE | D1 | 2020 | 21 | | COFFEE | D2 | 2020 | 22 | | COFFEE | D3 | 2020 | 23 | | COFFEE | D4 | 2020 | 24 | | COFFEE | D5 | 2020 | 25 | | TOFFEE | D1 | 2020 | 26 | | TOFFEE | D2 | 2020 | 27 | | TOFFEE | D3 | 2020 | 28 | | TOFFEE | D4 | 2020 | 29 | | TOFFEE | D5 | 2020 | 30 | +---------+--------+------+-------+我想要下面的輸出。我無法對數據進行透視，但無法在我移至列的行上使用滯後（如果有人可以建議任何其他功能）。下面是預期的輸出
+---------+------+----------------------------+--------------------------------------+ | | | Department | Difference in year department wise | +---------+------+----+-----+-----+-----+-----+-------+------+-------+-------+-------+ | Product | Year | D1 | D2 | D3 | D4 | D5 | D1D | D2D | D3D | D4D | D5D | +---------+------+----+-----+-----+-----+-----+-------+------+-------+-------+-------+ | TEA | 2018 | 40 | 30 | 20 | 10 | 7 | 40 | 30 | 20 | 10 | 7 | | TEA | 2019 | 1 | 2 | 3 | 4 | 5 | -39 | -28 | -17 | -6 | -2 | | TEA | 2020 | 16 | 17 | 18 | 19 | 20 | 15 | 15 | 15 | 15 | 15 | | COFFEE | 2018 | 70 | 100 | 200 | 300 | 400 | 70 | 100 | 200 | 300 | 400 | | COFFEE | 2019 | 6 | 7 | 8 | 9 | 10 | -64 | -93 | -192 | -291 | -390 | | COFFEE | 2020 | 21 | 22 | 23 | 24 | 25 | 15 | 15 | 15 | 15 | 15 | | TOFFEE | 2018 | 30 | 7 | 400 | 34 | 90 | 30 | 7 | 400 | 34 | 90 | | TOFFEE | 2019 | 11 | 12 | 13 | 14 | 15 | -19 | 5 | -387 | -20 | -75 | | TOFFEE | 2020 | 26 | 27 | 28 | 29 | 30 | 15 | 15 | 15 | 15 | 15 | +---------+------+----+-----+-----+-----+-----+-------+------+-------+-------+-------+
因為您只提供了圖片，所以我用不同的數據填充了表格，但這也應該適用於您的情況。這是你想要的？
編輯： 我將查詢更改為動態的。這應該可以解決問題。當然，您必須將表名#tbl 更改為您的表名。
/*Creating a table and populating it*/ CREATE TABLE #tbl (product nvarchar(50), region nvarchar(10), [year] int, sales int); DECLARE @sql nvarchar(max); DECLARE @columnname nvarchar(max); INSERT INTO #tbl(product, region, [year], sales) SELECT 'TEA', 'D1', 2018, 40 UNION ALL SELECT 'TEA', 'D2', 2018, 30 UNION ALL SELECT 'TEA', 'D3', 2018, 20 UNION ALL SELECT 'TEA', 'D4', 2018, 10 UNION ALL SELECT 'TEA', 'D5', 2018, 7 UNION ALL SELECT 'COFFEE', 'D1', 2018, 70 UNION ALL SELECT 'COFFEE', 'D2', 2018, 100 UNION ALL SELECT 'COFFEE', 'D3', 2018, 200 UNION ALL SELECT 'COFFEE', 'D4', 2018, 300 UNION ALL SELECT 'COFFEE', 'D5', 2018, 400 UNION ALL SELECT 'TEA', 'D1', 2019, 1 UNION ALL SELECT 'TEA', 'D2', 2019, 2 UNION ALL SELECT 'TEA', 'D3', 2019, 3 UNION ALL SELECT 'TEA', 'D4', 2019, 4 UNION ALL SELECT 'TEA', 'D5', 2019, 5 UNION ALL SELECT 'COFFEE', 'D1', 2019, 6 UNION ALL SELECT 'COFFEE', 'D2', 2019, 7 UNION ALL SELECT 'COFFEE', 'D3', 2019, 8 UNION ALL SELECT 'COFFEE', 'D4', 2019, 9 UNION ALL SELECT 'COFFEE', 'D5', 2019, 10 UNION ALL SELECT 'TEA', 'D1', 2020, 13 UNION ALL SELECT 'TEA', 'D2', 2020, 26 UNION ALL SELECT 'TEA', 'D3', 2020, 39 UNION ALL SELECT 'TEA', 'D4', 2020, 52 UNION ALL SELECT 'TEA', 'D5', 2020, 65 UNION ALL SELECT 'COFFEE', 'D1', 2020, 78 UNION ALL SELECT 'COFFEE', 'D2', 2020, 91 UNION ALL SELECT 'COFFEE', 'D3', 2020, 104 UNION ALL SELECT 'COFFEE', 'D4', 2020, 117 UNION ALL SELECT 'COFFEE', 'D5', 2020, 130 ; SELECT @columnname=COALESCE(@columnname+ ',', '') + QUOTENAME(CAST(q.region AS nvarchar(20)),'[]') FROM (SELECT DISTINCT t.region FROM #tbl t UNION ALL SELECT DISTINCT t.region+'D' AS region FROM #tbl t) AS q /*for your case, just replace the table name #tbl with the name of your table*/ SET @sql=' SELECT product, [year], '+@columnname+' FROM (SELECT product, CASE WHEN salee=''diff'' THEN region+''D'' ELSE region END AS sales, [year], sale FROM (SELECT t.product, t.region, t.[year], t.sales, ISNULL((t.sales - LAG(t.sales) OVER(PARTITION BY t.product, t.region order by t.[year] asc)),t.sales) AS diff FROM #tbl t) AS a UNPIVOT (sale for salee IN (sales, diff)) AS up) AS q PIVOT (MAX(sale) FOR sales IN ('+@columnname+')) AS p ORDER BY product' EXECUTE sp_executesql @sql輸出：
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+ | product | year | D1 | D2 | D3 | D4 | D5 | D1D | D2D | D3D | D4D | D5D | +=========+======+====+=====+=====+=====+=====+=====+=====+======+======+======+ | COFFEE | 2018 | 70 | 100 | 200 | 300 | 400 | 70 | 100 | 200 | 300 | 400 | +---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+ | COFFEE | 2019 | 6 | 7 | 8 | 9 | 10 | -64 | -93 | -192 | -291 | -390 | +---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+ | COFFEE | 2020 | 78 | 91 | 104 | 117 | 130 | 72 | 84 | 96 | 108 | 120 | +---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+ | TEA | 2018 | 40 | 30 | 20 | 10 | 7 | 40 | 30 | 20 | 10 | 7 | +---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+ | TEA | 2019 | 1 | 2 | 3 | 4 | 5 | -39 | -28 | -17 | -6 | -2 | +---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+ | TEA | 2020 | 13 | 26 | 39 | 52 | 65 | 12 | 24 | 36 | 48 | 60 | +---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+