反转列SQL服务器 [英] rEVERSING COLUMNS SQL SERVER
本文介绍了反转列SQL服务器的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的数据
I HAVE DATA LIKE THIS
DATE ID CATEGORYCODE AMOUNT
1/1/2013 100 DRIVER 10
1/1/2013 100 SALESMAN 100
1/1/2013 100 SALESEXEC 40
2/1/2013 100 DRIVER 60
< br $>
WANT OUT PUT LIKE THIS
DATE 1D DRIVER SALESMAN SALESEXEC
1/1/2013 100 10 100 40
2/1/2013 100 60
推荐答案
试试这个:
Try this:
SELECT [DATE], [DRIVER], [SALESMAN], [SALESEXEC]
FROM (SELECT * FROM YourTable) AS DT
PIVOT(SUM([AMOUNT]) FOR [ID] IN ([DRIVER], [SALESMAN], [SALESEXEC])) AS PT
ORDER BY [DATE]
如果您需要动态列,请尝试以下方法:
If you need dynamic columns, try this:
DECLARE @cols NVARCHAR(200)
SET @cols = STUFF((SELECT DISTINCT '],[' + [CATEGORYCODE]
FROM YourTable
ORDER BY '],[' + [CATEGORYCODE]
FOR XML PATH('')),1,2,'') + ']'
SELECT [DATE], @cols
FROM (SELECT * FROM YourTable) AS DT
PIVOT(SUM([AMOUNT]) FOR [ID] IN (@cols)) AS PT
ORDER BY [DATE]
[/ EDIT]
[/EDIT]
这篇关于反转列SQL服务器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文