SqlServer行转列&列半路出家

详细实施地址

USE Test_DB
GO

CREATE TABLE #TempSubjectResult
(
    StudentName NVARCHAR(50) NOT NULL,
    StudentSubject NVARCHAR(50) NOT NULL,
    StudentGrade DECIMAL(4,1) NOT NULL
)

INSERT INTO #TempSubjectResult(StudentName,StudentSubject,StudentGrade) VALUES (小姚,语文,80)
INSERT INTO #TempSubjectResult(StudentName,StudentSubject,StudentGrade) VALUES (小姚,数学,70)
INSERT INTO #TempSubjectResult(StudentName,StudentSubject,StudentGrade) VALUES (小姚,英语,60)
INSERT INTO #TempSubjectResult(StudentName,StudentSubject,StudentGrade) VALUES (小明,语文,90)
INSERT INTO #TempSubjectResult(StudentName,StudentSubject,StudentGrade) VALUES (小明,数学,95)
INSERT INTO #TempSubjectResult(StudentName,StudentSubject,StudentGrade) VALUES (小明,英语,98)

SELECT * FROM #TempSubjectResult FOR XML PATH

-- 1 Case When普通版
SELECT StudentName 学生名,
       MAX(CASE WHEN StudentSubject=语文 THEN StudentGrade END) 语文,
       MAX(CASE WHEN StudentSubject=数学 THEN StudentGrade END) 数学,
       MAX(CASE WHEN StudentSubject=英语 THEN StudentGrade END) 英语
  FROM #TempSubjectResult
 GROUP BY StudentName

-- 2 PIVOT普通版
SELECT * FROM #TempSubjectResult PIVOT(MAX(StudentGrade) FOR StudentSubject IN([语文],[数学],[英语])) T

-- 3 Case When升级版 动态SQL
DECLARE @SqlText NVARCHAR(4000)=SELECT StudentName 学生名,  -- SQL头部分 SELECT
SELECT @SqlText=@SqlText+ MAX(CASE WHEN StudentSubject=+StudentSubject+ THEN StudentGrade ELSE 0 END)  + StudentSubject + , FROM (SELECT DISTINCT StudentSubject FROM #TempSubjectResult) T -- 拼接CASE WHEN
SELECT @SqlText= LEFT(@SqlText,LEN(@SqlText)-1)+ FROM #TempSubjectResult GROUP BY StudentName -- 拼接 FROM后面部分

PRINT @SqlText
EXEC (@SqlText)

-- 4 PIVOT升级版 动态SQL
DECLARE @SqlSubject NVARCHAR(4000)
SELECT @SqlSubject= STUFF((SELECT ,+[+StudentSubject+] FROM (SELECT DISTINCT StudentSubject FROM #TempSubjectResult) T  FOR XML PATH()),1,1,) -- 获取PIVOT科目
DECLARE @SqlPIVOT NVARCHAR(4000)=SELECT * FROM #TempSubjectResult PIVOT(MAX(StudentGrade) FOR StudentSubject IN(+@SqlSubject+)) T -- 拼接PIVOT
PRINT @SqlPIVOT
EXEC (@SqlPIVOT)
--DROP TABLE #TempSubjectResult

SELECT * FROM #TempSubjectResult

SELECT StudentName, MAX(CASE WHEN StudentSubject=语文 THEN StudentGrade ELSE 0 END) 语文,
MAX(CASE WHEN StudentSubject=数学 THEN StudentGrade ELSE 0 END) 数学,
MAX(CASE WHEN StudentSubject=英语 THEN StudentGrade ELSE 0 END) 英语
FROM #TempSubjectResult
GROUP BY StudentName