27,579
社区成员
发帖
与我相关
我的任务
分享
with tb(姓名,课程,分数)
as(
SELECT'张三','语文',85
UNION ALL SELECT'李四','语文',92
UNION ALL SELECT'王五','语文',88
UNION ALL SELECT'李四','数学',64
UNION ALL SELECT'张三','英语',77
UNION ALL SELECT'王五','英语',81
)
SELECT * FROM tb pivot( MAX(分数) FOR 课程 IN (语文,数学,英语))a
--以下是2005+可以用的写法
--动态(科目的顺序会按拼音顺序)
DECLARE @SQL VARCHAR(MAX)
SET @SQL=''
SELECT @SQL=@SQL+',['+Subject+']'
FROM A GROUP BY Subject
SET @SQL='SELECT * FROM (SELECT Name,Subject,Mark FROM A)T PIVOT(MAX(Mark)FOR Subject IN('
+STUFF(@SQL,1,1,'')+'))P'
--PRINT @SQL
EXEC(@SQL)
--静态
SELECT * FROM
(SELECT Name,Subject,Mark FROM A)T
PIVOT(MAX(Mark)FOR Subject IN([语文],[数学],[英语]))P
--动态(科目的顺序会按拼音顺序)
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT Name'
SELECT @SQL=@SQL+',SUM(CASE WHEN Subject='''+Subject+''' THEN Mark END)['+Subject+']'
FROM A GROUP BY Subject
SET @SQL=@SQL+'FROM A GROUP BY Name'
PRINT @SQL
EXEC(@SQL)
--静态
SELECT Name
,SUM(CASE WHEN Subject='语文' THEN Mark END)[语文]
,SUM(CASE WHEN Subject='数学' THEN Mark END)[数学]
,SUM(CASE WHEN Subject='英语' THEN Mark END)[英语]
FROM A GROUP BY Name