SQL SERVER 2005+å®ç°è¡åäºè½¬å¯ä»¥ç¨PIVOTåUNPIVOT
è¿ä¸ªæ¯å®ç°ï¼
SELECT * FROM (
SELECT sc.student_id,st.name,curse_name,score FROM student st JOIN score sc ON st.id=sc.student_id) AS T
PIVOT(SUM(score) FOR curse_name IN (è¯æ,æ°å¦)) AS P
ä¸è¿è¿ä¸æ¯æå®æ´ç解å³æ¹æ¡ï¼åè®¾ä½ æ·»å å¤äºå é¨è¯¾ç¨ï¼ä¾å¦è±è¯ï¼åå¦ï¼çç©ä¹ç±»çï¼å°±è¦å¨åæ¥çè¯å¥éæ·»å ç¸åºç项ï¼æ以è¦ç¨å¨æSQLæ¥å®ç°ï¼æ¹æ³å°±æ¯å
æscore表éçå
¨é¨è¯¾ç¨GROUP BY åºæ¥ï¼çæå符串形å¼ï¼å¦âè¯æ,æ°å¦,è±è¯,åå¦,çç©âï¼ï¼ç¨éå·éå¼ï¼æå
¥å°ä¸é¢è¯å¥çPIVOT表达å¼çINä¸ï¼å®ç°å¦ä¸ï¼
DECLARE @sql NVARCHAR(MAX)
DECLARE @curseList NVARCHAR(200)
SET @curseList=STUFF(
(SELECT ','+curse_name FROM score GROUP BY curse_name FOR XML PATH('')),1,1,'')
SET @sql='
SELECT * FROM (
SELECT student_id,name AS sdudent_name,curse_name,score FROM student st JOIN score sc ON st.id=sc.student_id) AS T
PIVOT(SUM(score) FOR curse_name IN ('+@curseList+')) AS P'
EXEC(@sql)
温馨提示:答案为网友推荐,仅供参考