存储过程行转列

CREATE TABLE [dbo].[Test]
(
[Id] int IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Subject] [nvarchar](50) NULL,
[Score] [numeric](18, 0) NULL
)
INSERT INTO [Test] ([Name],[Subject],[Score])
SELECT '张一','语文',60 UNION ALL
SELECT '张二','数学',70 UNION ALL
SELECT '张三','英语',80 UNION ALL
SELECT '张一','数学',75 UNION ALL
SELECT '张二','语文',57 UNION ALL
SELECT '张三','语文',80 UNION ALL
SELECT '张一','英语',100

DECLARE @sql_str VARCHAR(8000)
DECLARE @sql_col VARCHAR(8000)
SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME([Subject]) FROM [Test] GROUP BY [Subject]
SET @sql_str = '
SELECT * FROM (
SELECT [Name],[Subject],[Score] FROM [Test]) p PIVOT
(SUM([Score]) FOR [Subject] IN ( '+ @sql_col +') ) AS pvt
ORDER BY pvt.[UserName]'
PRINT (@sql_str)
EXEC (@sql_str)
GO
  DECLARE @sql_col2 VARCHAR(8000)
  SELECT    @sql_col2 = ISNULL(@sql_col2 + ',','') + QUOTENAME(Subject) FROM Test GROUP BY Subject
    PRINT (@sql_col2)