sql server行轉列

首先建立表和添加資料

想要的結果

--方法一SELECT DISTINCT C.[USER_NAME] AS '姓名',(SELECT SCORE FROM TEST_TB_GRADE WHERE [USER_NAME]=C.[USER_NAME] AND COURSE='語文') AS '語文',(SELECT SCORE FROM TEST_TB_GRADE WHERE [USER_NAME]=C.[USER_NAME] AND COURSE='數學') AS '數學',(SELECT SCORE FROM TEST_TB_GRADE WHERE [USER_NAME]=C.[USER_NAME] AND COURSE='英語') AS '英語'FROM dbo.TEST_TB_GRADE C--方法二SELECT [USER_NAME] AS '姓名',SUM(CASE WHEN COURSE='語文' THEN SCORE END) AS '語文',SUM(CASE WHEN COURSE='數學' THEN SCORE END) AS '數學',SUM(CASE WHEN COURSE='英語' THEN SCORE END) AS '英語'FROM dbo.TEST_TB_GRADEGROUP BY [USER_NAME]--方法三DECLARE @S NVARCHAR(1000)SET @S='SELECT [USER_NAME] AS '''+'姓名'+''''SELECT @S=@S+',SUM(CASE WHEN COURSE='''+CAST(COURSE AS VARCHAR)+'''THEN SCORE END) AS '''+COURSE+'''' FROM TEST_TB_GRADE GROUP BY COURSE 
SET @S=@S+' FROM TEST_TB_GRADE GROUP BY [USER_NAME]' EXEC(@S)

　　推薦方法三