Sql数据透视表查询问题
问题描述：
你好，
我总是被Pivoting表所困，我觉得这是sql中最难的话题，
下面是我想要的预期结果，有人可以帮忙吗？
主表
Hello,
I am always stuck with Pivoting tables, I feel it is most difficult topic in sql,
Below is the expected result which i want, can somebody help?
Main Table
ProductType | YTD_SPD | Var_SPD | Per_SPD
OutboundNON DOC | 2448.029903 | 244.0843848 | 11.07488288
InboundNON DOC | 364.819701 | 68.18525457 | 22.98629016
预期产出
Output Expected
ProductType | OutboundNON DOC | InboundNON DOC
YTD_SPD | 2448.029903 | 364.819701
Var_SPD | 244.0843848 | 68.18525457
Per_SPD | 11.07488288 | 22.98629016
我尝试过：
What I have tried:
SELECT 'YTD_SPD' ProductType,
[OutboundNON DOC], [InboundNON DOC],[InboundDOC],[OutboundDOC]
FROM
(SELECT ProductType,YTD_SPD, Var_SPD from Product_Performance2) AS SourceTable
PIVOT
(
SUM(Var_SPD)
FOR ProductType IN ([OutboundNON DOC],[InboundNON DOC],[InboundDOC],[OutboundDOC])
) AS PivotTable;
答
你不能只是转动数据，你需要先取消它。检查一下：
You can't just pivot data, you need to unpivot it first. Check this:
DECLARE @tmp TABLE (ProductType NVARCHAR(30), YTD_SPD DECIMAL(20,8), Var_SPD DECIMAL(20,8), Per_SPD DECIMAL(20,8))
INSERT INTO @tmp (ProductType, YTD_SPD, Var_SPD, Per_SPD)
VALUES('OutboundNON DOC', 2448.029903, 244.0843848, 11.07488288),
('InboundNON DOC', 364.819701, 68.18525457, 22.98629016)
--SELECT ColName As ProductType, [OutboundNON DOC], [InboundNON DOC]
--FROM (
SELECT ColName, ProductType, Data
FROM (
SELECT *
FROM @tmp
) AS pvt1
UNPIVOT(Data FOR ColName IN (YTD_SPD, Var_SPD, Per_SPD)) AS unpvt
--) AS src
--PIVOT(SUM(Data) FOR ProductType IN ([OutboundNON DOC], [InboundNON DOC])) AS pvt2
-- ==================================
-- Above code returns unpivoted form
-- Uncomment lines to see final form
-- ==================================
请参阅MSDN文档：使用PIVOT和UNPIVOT | Microsoft Docs [ ^ ]