我使用以下SQL查询返回一个包含4列的表,Year
,Month
,Quantity Sold
,Stock_Code
,
SELECT yr, mon, sum(Quantity) as Quantity, STOCK_CODE
FROM [All Stock Purchased]
group by yr, mon, stock_code
order by yr, mon, stock_code
这是一些数据的一个例子,但我有大约 3000 个Stock_Codes和大约 40 x yr/mon 组合。
yr mon Quantity STOCK_CODE
2015 4 42 100105
2015 4 220 100135
2015 4 1 100237
2015 4 2 100252
2015 4 1 100277
我想将其透视到一个表中,该表为每个SKU提供一行,每个年/月组合都有列。
我以前从未使用过 Pivot,所以做了一些研究并创建了一个我认为应该有效的 SQL 查询。
select * from
(SELECT yr,
mon, Quantity,
STOCK_CODE
FROM [All Stock Purchased]) AS BaseData
pivot (
sum(Quantity)
For Stock_Code
in ([4 2015],[5 2015] ...........
) as PivotTable
此查询返回一个表,其中 Yr 作为 col1,Mon 作为 col2,然后 4 2015 等作为后续列。而我希望 col1 是Stock_Code的,col2 是 2015 年 4 日售出的股票代码的数量。
真的很想了解我上面的代码有什么问题。
以下使用动态PIVOT
的查询应执行所需的操作:
CREATE TABLE #temp (Yr INT,Mnt INT,Quantity INT, Stock_Code INT)
INSERT INTO #temp VALUES
(2015,4,42,100105),
(2015,4,100,100105),
(2015,5,220,100135),
(2015,4,1,100237),
(2015,4,2,100252),
(2015,7,1,100277)
DECLARE @pvt NVARCHAR(MAX) = '';
SET @pvt = STUFF(
(SELECT DISTINCT N', ' + QUOTENAME(CONVERT(VARCHAR(10),Mnt) +' '+ CONVERT(VARCHAR(10),Yr)) FROM #temp FOR XML PATH('')),1,2,N'');
EXEC (N'
SELECT pvt.* FROM (
SELECT Stock_Code
,CONVERT(VARCHAR(10),Mnt) +'' ''+ CONVERT(VARCHAR(10),Yr) AS [Tag]
,Quantity
FROM #temp )a
PIVOT (SUM(Quantity) FOR [Tag] IN ('+@pvt+')) pvt');
结果如下,
Stock_Code 4 2015 5 2015 7 2015
100105 142 NULL NULL
100135 NULL 220 NULL
100237 1 NULL NULL
100252 2 NULL NULL
100277 NULL NULL 1
您可以在不使用透视的情况下实现此目的。
SELECT P.`STOCK_CODE`,
SUM(
CASE
WHEN P.`yr`=2015 AND P.`mon` = '1'
THEN P.`Quantity`
ELSE 0
END
) AS '1 2015',
SUM(
CASE
WHEN P.`yr`=2015 AND P.`mon` = '2'
THEN P.`Quantity`
ELSE 0
END
) AS '2 2015',
SUM(
CASE
WHEN P.`yr`=2015 AND P.`mon` = '3'
THEN P.`Quantity`
ELSE 0
END
) AS '3 2015',
FROM [All Stock Purchased] P
GROUP BY P.`STOCK_CODE`;