按年份的多个度量标准的SQL Pivot [英] SQL Pivot for Multiple Metrics by Year
本文介绍了按年份的多个度量标准的SQL Pivot的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
有没有一种方法可以在1个SQL数据透视运算符中显示多个指标.基本上,我有Table1,并且希望得到的结果是Table2格式.
Is there a way to show multiple metrics in 1 SQL pivot operator. Basically, I have the Table1 and want the desired results is the Table2 format.
Table1
ACCOUNTS YEAR REVENUE MARGIN
ACCOUNT1 2012 100 50
ACCOUNT1 2013 104 52
ACCOUNT1 2014 108 54
ACCOUNT2 2012 112 56
ACCOUNT2 2013 116 58
ACCOUNT2 2014 120 60
ACCOUNT3 2012 124 62
ACCOUNT3 2013 128 64
ACCOUNT3 2014 132 66
Table2
ACCOUNTS REVENUE_2012 REVENUE_2013 REVENUE_2014 MARGIN_2012 MARGIN_2013 MARGIN_2014
ACCOUNT1 100 104 108 50 52 54
ACCOUNT2 112 116 120 56 58 60
ACCOUNT3 124 128 132 62 64 66
请帮助
推荐答案
DECLARE @t TABLE
(
ACCOUNTS NVARCHAR(MAX) ,
YEAR INT ,
REVENUE INT ,
MARGIN INT
)
INSERT INTO @t
VALUES ('ACCOUNT1', 2012, 100, 50 ),('ACCOUNT1', 2013, 104, 52 ),
('ACCOUNT1', 2014, 108, 54 ),('ACCOUNT2', 2012, 112, 56 ),
('ACCOUNT2', 2013, 116, 58 ),('ACCOUNT2', 2014, 120, 60 ),
('ACCOUNT3', 2012, 124, 62 ),('ACCOUNT3', 2013, 128, 64 ),
('ACCOUNT3', 2014, 132, 66 )
;WITH CTE AS
(
SELECT ACCOUNTS, value, name + '_' + cast(YEAR as char(4)) header
FROM @t as p
UNPIVOT
(value FOR name IN
([REVENUE], [MARGIN]) ) AS unpvt
)
SELECT ACCOUNTS, [REVENUE_2012],[REVENUE_2013],[REVENUE_2014]
,[MARGIN_2012],[MARGIN_2013],[MARGIN_2014]
FROM CTE
PIVOT
(SUM([value])
FOR header
in([REVENUE_2012],[REVENUE_2013],[REVENUE_2014], [MARGIN_2012]
,[MARGIN_2013],[MARGIN_2014])
)AS p ORDER BY 2,3,4
结果:
ACCOUNTS REVENUE_2012 REVENUE_2013 REVENUE_2014 MARGIN_2012 MARGIN_2013 MARGIN_2014
ACCOUNT1 100 104 108 50 52 54
ACCOUNT2 112 116 120 56 58 60
ACCOUNT3 124 128 132 62 64 66
这篇关于按年份的多个度量标准的SQL Pivot的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文