Sql数据透视表查询问题 [英] Sql pivot table query issue

问题描述

你好，



我总是被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 [ ^ ]

这篇关于Sql数据透视表查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！