SQL多行转多列怎么实现呢

问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
张三 德语 null
李四 语文 74
李四 数学 84
李四 物理 94
李四 英语 80
想变成(得到如下结果)： 
姓名 语文 数学 物理 英语 德语
---- ---- ---- ----
李四 74   84   94  ？ 60
张三 74   83   93  80 ？
-------------------
怎么实现这个这个功能呢，请大神指教，多谢啦

30 个解决方案

#1

select 姓名,
sum(case when 课程='语文' then 分数 else 0 end)  语文,
sum(case when 课程='数学' then 分数 else 0 end)  数学,
sum(case when 课程='物理' then 分数 else 0 end)  物理,
sum(case when 课程='英语' then 分数 else 0 end)  英语,
sum(case when 课程='德语' then 分数 else 0 end)  德语
from tb
group by 姓名

#2

你参考一下

SELECT P.* FROM [学生成绩表]PIVOT(MAX([分数])FOR[课程]IN([语文],[数学],[物理],[英语],[德语]))P

如果课程不固定，还可以用动态处理

#3

select a.* from tb pivot(sum(分数) for 课程 in(语文,数学,物理,英语,德语))  as a

#4

#5

引用 2 楼 ky_min 的回复:

你参考一下

SELECT P.* FROM [学生成绩表]PIVOT(MAX([分数])FOR[课程]IN([语文],[数学],[物理],[英语],[德语]))P

如果课程不固定，还可以用动态处理

动态怎么处理呢，不同人选的课程不一样，有的学生课程成绩还为空，就设为60，没选的课程成绩设置为？

#6

引用 4 楼 gqqcookle 的回复:

课程不固定呢，毕竟如果课程多的话，枚举也太麻烦了，还有学生选的课程中没成绩的设为60，学生没选的课程的成绩设为？，这样的怎么实现呢，都怪我没描述清楚

#7

引用 5 楼 keanmanli 的回复:

Quote: 引用 2 楼 ky_min 的回复:

你参考一下

SELECT P.* FROM [学生成绩表]PIVOT(MAX([分数])FOR[课程]IN([语文],[数学],[物理],[英语],[德语]))P

如果课程不固定，还可以用动态处理

动态怎么处理呢，不同人选的课程不一样，有的学生课程成绩还为空，就设为60，没选的课程成绩设置为？

SELECT [姓名]
	,ISNULL(MAX(CASE [课程]WHEN '语文'THEN [分数]END ),60)[语文]
	,ISNULL(MAX(CASE [课程]WHEN'数学'THEN[分数]END),60)[数学]
	,ISNULL(MAX(CASE [课程]WHEN'物理'THEN[分数]END),60)[物理]
	,ISNULL(MAX(CASE [课程]WHEN'英语'THEN[分数]END),60)[英语]
	,ISNULL(MAX(CASE [课程]WHEN'德语'THEN[分数]END),60)[德语]
FROM [学生成绩表]
GROUP BY [姓名]

#8

动态的话，你有课程表吗?记录有哪些课程
如果没有也可以~~

#9

引用 6 楼 keanmanli 的回复:

Quote: 引用 4 楼 gqqcookle 的回复:

课程不固定呢，毕竟如果课程多的话，枚举也太麻烦了，还有学生选的课程中没成绩的设为60，学生没选的课程的成绩设为？，这样的怎么实现呢，都怪我没描述清楚

#10

引用 8 楼 ky_min 的回复:

动态的话，你有课程表吗?记录有哪些课程
如果没有也可以~~

有课程表，但是课程有几百个，不能枚举到case吧，怎么解决呢

#11

DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT[姓名]'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE[课程]WHEN '''+[课程]+'''THEN [分数]END ),60)['+[课程]+']'FROM(SELECT[课程]FROM[学生成绩表]GROUP BY[课程])C
SET @SQL=@SQL+'FROM[学生成绩表]GROUP BY[姓名]'
EXEC(@SQL)

#12

引用 10 楼 keanmanli 的回复:

Quote: 引用 8 楼 ky_min 的回复:

动态的话，你有课程表吗?记录有哪些课程
如果没有也可以~~

有课程表，但是课程有几百个，不能枚举到case吧，怎么解决呢

你试下，如#11动态处理

#13

-- 用临时表做的测试数据
WITH course(ID,课程) AS (
    SELECT 1,'语文' UNION ALL
    SELECT 2,'数学' UNION ALL
    SELECT 3,'物理' UNION ALL
    SELECT 4,'英语' UNION ALL
    SELECT 5,'德语' UNION ALL
    SELECT 6,'法语'
)
SELECT *
  INTO #course
  FROM course;

WITH student(姓名) AS (
    SELECT '张三' UNION ALL
    SELECT '李四' UNION ALL
    SELECT '王五'
)
SELECT *
  INTO #student
  FROM student;

WITH tb (姓名,课程,分数) AS (
    SELECT '张三','语文',74 UNION ALL
    SELECT '张三','数学',83 UNION ALL
    SELECT '张三','物理',93 UNION ALL
    SELECT '张三','德语',null UNION ALL
    SELECT '李四','语文',74 UNION ALL
    SELECT '李四','数学',84 UNION ALL
    SELECT '李四','物理',94 UNION ALL
    SELECT '李四','英语',80 
)
SELECT *
  INTO #tb
  FROM tb;

-- 以下为查询部分，你自己改为实际的表名
DECLARE @sql varchar(max),
        @columns varchar(max)

SET @columns = ''

  SELECT  @columns = @columns + ', [' + [课程] + ']' 
    FROM #course
ORDER BY ID

SET @sql = '
    SELECT *
      FROM (
                SELECT s.姓名,
                       c.课程,
                       ISNULL(t.分数,60) AS 分数
                  FROM #student s
                  JOIN #course c
                    ON 1=1
             LEFT JOIN #tb t
                    ON t.姓名 = s.姓名
                   AND t.课程 = c.课程
           ) l
     PIVOT (
            Max (分数)
            FOR 课程 IN ( ' + SubString(@columns, 3, Len(@columns)-2) + ')
           ) AS p'
--PRINT @sql
EXEC (@sql)

姓名        语文        数学        物理        英语        德语        法语
---- ----------- ----------- ----------- ----------- ----------- -----------
李四          74          84          94          80          60          60
王五          60          60          60          60          60          60
张三          74          83          93          60          60          60

#14

引用 13 楼 Tiger_Zhao 的回复:

-- 用临时表做的测试数据
WITH course(ID,课程) AS (
    SELECT 1,'语文' UNION ALL
    SELECT 2,'数学' UNION ALL
    SELECT 3,'物理' UNION ALL
    SELECT 4,'英语' UNION ALL
    SELECT 5,'德语' UNION ALL
    SELECT 6,'法语'
)
SELECT *
  INTO #course
  FROM course;

WITH student(姓名) AS (
    SELECT '张三' UNION ALL
    SELECT '李四' UNION ALL
    SELECT '王五'
)
SELECT *
  INTO #student
  FROM student;

WITH tb (姓名,课程,分数) AS (
    SELECT '张三','语文',74 UNION ALL
    SELECT '张三','数学',83 UNION ALL
    SELECT '张三','物理',93 UNION ALL
    SELECT '张三','德语',null UNION ALL
    SELECT '李四','语文',74 UNION ALL
    SELECT '李四','数学',84 UNION ALL
    SELECT '李四','物理',94 UNION ALL
    SELECT '李四','英语',80 
)
SELECT *
  INTO #tb
  FROM tb;

-- 以下为查询部分，你自己改为实际的表名
DECLARE @sql varchar(max),
        @columns varchar(max)

SET @columns = ''

  SELECT  @columns = @columns + ', [' + [课程] + ']' 
    FROM #course
ORDER BY ID

SET @sql = '
    SELECT *
      FROM (
                SELECT s.姓名,
                       c.课程,
                       ISNULL(t.分数,60) AS 分数
                  FROM #student s
                  JOIN #course c
                    ON 1=1
             LEFT JOIN #tb t
                    ON t.姓名 = s.姓名
                   AND t.课程 = c.课程
           ) l
     PIVOT (
            Max (分数)
            FOR 课程 IN ( ' + SubString(@columns, 3, Len(@columns)-2) + ')
           ) AS p'
--PRINT @sql
EXEC (@sql)

姓名        语文        数学        物理        英语        德语        法语
---- ----------- ----------- ----------- ----------- ----------- -----------
李四          74          84          94          80          60          60
王五          60          60          60          60          60          60
张三          74          83          93          60          60          60

对于学生没选的课程的成绩设置为？，怎么处理呢？然后我的数据库表学生有几万个，课程也有上千个，这样枚举的话不合适吧，那这样该怎么处理呢

#15

ISNULL(t.分数,60) AS 分数

更改为

ISNULL(Convert(varchar(11),t.分数),'?') AS 分数

你既然要求 几万*上千的表格，除了乘积还能用什么方法？

#16

引用 8 楼 ky_min 的回复:

动态的话，你有课程表吗?记录有哪些课程
如果没有也可以~~

输出的结果
SELECT[XH],ISNULL(MAX(CASE[KCZWMC]WHEN '俄语入门'THEN [BFZCJ]END ),60)[俄语入门],ISNULL(MAX(CASE[KCZWMC]WHEN '女性学'THEN [BFZCJ]END ),60)[女性学],ISNULL(MAX(CASE[KCZWMC]WHEN '大学物理A2(西安交通大学)'THEN [BFZCJ]END ),60)[大学物理A2(西安交通大学)],ISNULL(MAX(CASE[KCZWMC]WHEN '英语阅读(教改试点三)'THEN [BFZCJ]END ),60)[英语阅读(教改试点三)],ISNULL(MAX(CASE[KCZWMC]WHEN '性学课程——爱欲与文明的对话'THEN [BFZCJ]END ),60)[中国近现代史刚要(西安交通大学)],ISNULL(MAX(CASE[KCZWMC]WHEN '毕业教育'THEN [BFZCJ]END ),60)[毕FROM [CourseMarkDB].[dbo].[CourseMark] GROUP BY[XH]

然后呢

#17

引用 15 楼 Tiger_Zhao 的回复:

ISNULL(t.分数,60) AS 分数

更改为

ISNULL(Convert(varchar(11),t.分数),'?') AS 分数

你既然要求 几万*上千的表格，除了乘积还能用什么方法？

新手不懂啊，不知道怎么实现，只是急需要数据做实验，然后需要改变数据格式

#18

),60)[毕
这儿是你没贴全，还是真的是这样?

DECLARE @SQL NVARCHAR(MAX)
SET @SQL='SELECT[姓名]'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE[课程]WHEN '''+[课程]+'''THEN [分数]END ),60)['+[课程]+']'FROM(SELECT[课程]FROM[学生成绩表]GROUP BY[课程])C
SET @SQL=@SQL+'FROM[学生成绩表]GROUP BY[姓名]'
EXEC(@SQL)

试下这个

#19

Quote: 引用 18 楼 ky_min 的回复:

),60)[毕
这儿是你没贴全，还是真的是这样?

DECLARE @SQL NVARCHAR(MAX)
SET @SQL='SELECT[姓名]'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE[课程]WHEN '''+[课程]+'''THEN [分数]END ),60)['+[课程]+']'FROM(SELECT[课程]FROM[学生成绩表]GROUP BY[课程])C
SET @SQL=@SQL+'FROM[学生成绩表]GROUP BY[姓名]'
EXEC(@SQL)
没贴全，数据太多了，不能回复，我就把中间的大部分数据给删了

执行下面那个，出错：
消息 105，级别 15，状态 1，第 1 行
字符串 '编FROM [CourseMarkDB].[dbo].[CourseMark]GROUP BY[XH]' 后的引号不完整。
消息 102，级别 15，状态 1，第 1 行
'编FROM [CourseMarkDB].[dbo].[CourseMark]GROUP BY[XH]' 附近有语法错误。

#20

应该是课程太多了,你试下这个
DECLARE @SQL VARCHAR(8000)
DECLARE @PIVOT VARCHAR(8000)
SET @SQL='SELECT[姓名]'
SET @PIVOT=''
SELECT @SQL=@SQL+',ISNULL(['+[课程]+'],60)['+[课程]+']',@PIVOT=@PIVOT+',['+[课程]+']'FROM(SELECT[课程]FROM[学生成绩表]GROUP BY[课程])C
SET @SQL=@SQL+'FROM[学生成绩表]PIVOT(MAX([分数])FOR[课程]IN('+STUFF(@PIVOT,1,1,'')+'))P'
EXEC(@SQL)

#21

引用 17 楼 keanmanli 的回复:

新手不懂啊，不知道怎么实现，只是急需要数据做实验，然后需要改变数据格式

结果有了不是？
先实现了再说！

#22

引用 20 楼 ky_min 的回复:

应该是课程太多了,你试下这个
DECLARE @SQL VARCHAR(8000)
DECLARE @PIVOT VARCHAR(8000)
SET @SQL='SELECT[姓名]'
SET @PIVOT=''
SELECT @SQL=@SQL+',ISNULL(['+[课程]+'],60)['+[课程]+']',@PIVOT=@PIVOT+',['+[课程]+']'FROM(SELECT[课程]FROM[学生成绩表]GROUP BY[课程])C
SET @SQL=@SQL+'FROM[学生成绩表]PIVOT(MAX([分数])FOR[课程]IN('+STUFF(@PIVOT,1,1,'')+'))P'
EXEC(@SQL)

这次出错的更奇怪：
消息 102，级别 15，状态 1，第 1 行
'.' 附近有语法错误。
消息 105，级别 15，状态 1，第 1 行
字符串 '大学' 后的引号不完整。
都不知道大学字符串哪里冒出来的

#23

照旧把
EXEC(@SQL)
换成PRINT @SQL 
然后贴上来，有多少贴多少~~

#24

引用 23 楼 ky_min 的回复:

照旧把
EXEC(@SQL)
换成PRINT @SQL 
然后贴上来，有多少贴多少~~

SELECT[XH],ISNULL([俄语入门],60)[俄语入门],ISNULL([女性学],60)[女性学],ISNULL([大学物理A2(西安交通大学)],60)[大学物理A2(西安交通大学)]FROM [CourseMarkDB].[dbo].[CourseMark] PIVOT(MAX([BFZCJ])FOR[KCZWMC]IN([俄语入门],[女性学],[大学
全部复制显示有非法词组，无法发布，我省略了一下，但是保留了主要内容，去掉的都是一些重复的

#25

引用 24 楼 keanmanli 的回复:

Quote: 引用 23 楼 ky_min 的回复:

照旧把
EXEC(@SQL)
换成PRINT @SQL 
然后贴上来，有多少贴多少~~

SELECT[XH],ISNULL([俄语入门],60)[俄语入门],ISNULL([女性学],60)[女性学],ISNULL([大学物理A2(西安交通大学)],60)[大学物理A2(西安交通大学)]FROM [CourseMarkDB].[dbo].[CourseMark] PIVOT(MAX([BFZCJ])FOR[KCZWMC]IN([俄语入门],[女性学],[大学
全部复制显示有非法词组，无法发布，我省略了一下，但是保留了主要内容，去掉的都是一些重复的

去省略的是中间的内容，首尾就是上面那样的

#26

从报错上，我怀疑语句不整，可能因课程太多，引起字符串长度不够
或者你的有的课程名称有特殊字符~

#27

把VARCHAR(8000)换成VARCHAR(MAX)试下

#28

引用 27 楼 ky_min 的回复:

把VARCHAR(8000)换成VARCHAR(MAX)试下

这样读出来的更少了，就只到ISNULL([家用电子设备的原理、使用和维护],60)[家用电子设备的原，后面那部分都没打印出来了

#29

我 #13 的动态代码应该比较短，可以执行不？

#30

比较简单的方法：用JDBC，建立临时表，一条一条读到VO中，再互转VO，存进新建的表中。