反转列SQL服务器 [英] rEVERSING COLUMNS SQL SERVER

问题描述

我有这样的数据

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屋！