SQL 动态PIVOT查询

DECLARE @sql_str VARCHAR(8000)
DECLARE @sql_col VARCHAR(8000)

SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME([indicator]) FROM [sizelist] GROUP BY [Indicator]

SET @sql_str = '
SELECT * FROM (
SELECT [Country],[gender],sku,[indicator],[sizename] FROM [sizelist]) p PIVOT
(max([sizename]) FOR [indicator] IN ( '+ @sql_col +') ) AS pvt
ORDER BY pvt.[Country]'
PRINT (@sql_str)
EXEC (@sql_str)

 

 

 

另外使用SQL方式

DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT [Name],'
SELECT @sql = @sql + 'SUM(CASE [Subject] WHEN '''+[Subject]+''' THEN [score] ELSE 0 END) AS '''+QUOTENAME([Subject])+''','
FROM (SELECT DISTINCT [Subject] FROM [student2]) AS a

SELECT @sql = LEFT(@sql,LEN(@sql)-1) + ' FROM student2 GROUP BY Name'
PRINT(@sql)
EXEC(@sql)
GO