如何在SQL Server中使用pivot来制作这样的表?

Question

假设我有一个这样的表

<表类> 代码 名称 预算 tbody><<tr>1111ABC15001111DEF20001111QWE30002222ABC18002222DEF10003333QWE1500

您可以通过同时使用Pivot和动态查询来实现:

DECLARE @cols AS NVARCHAR(MAX)
,@query AS NVARCHAR(MAX);
SET @cols = STUFF((
SELECT ',' + QUOTENAME(c.[Name])
FROM (
SELECT DISTINCT [Name]
FROM Budget
) c
ORDER BY [Name]
FOR XML PATH('')
,TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SELECT @cols

SET @query = N'SELECT *
from
(SELECT Code,Name,Budget FROM budget )
AS source
PIVOT
(
sum(Budget) 
FOR [Name] IN ( '+@cols+')
)AS PivotTable
';
EXECUTE (@query)