SQL多行转多列怎么实现呢

keanmanli 2014-10-22 09:43:43
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
张三 德语 null
李四 语文 74
李四 数学 84
李四 物理 94
李四 英语 80
想变成(得到如下结果)：
姓名 语文 数学 物理 英语 德语
---- ---- ---- ----
李四 74 84 94 ？ 60
张三 74 83 93 80 ？
-------------------
怎么实现这个这个功能呢，请大神指教，多谢啦
...全文
1574 30 打赏 收藏 转发到动态 举报
写回复
用AI写文章
30 条回复
切换为时间正序
请发表友善的回复…
发表回复
还在加载中灬 2014-10-22
  • 打赏
  • 举报
 回复
照旧把 EXEC(@SQL) 换成PRINT @SQL 然后贴上来，有多少贴多少~~
keanmanli 2014-10-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 行 字符串 '大学' 后的引号不完整。 都不知道大学字符串哪里冒出来的
Tiger_Zhao 2014-10-22
  • 打赏
  • 举报
 回复
引用 17 楼 keanmanli 的回复:
新手不懂啊，不知道怎么实现，只是急需要数据做实验，然后需要改变数据格式
结果有了不是？ 先实现了再说！
还在加载中灬 2014-10-22
  • 打赏
  • 举报
 回复
应该是课程太多了,你试下这个 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)
keanmanli 2014-10-22
  • 打赏
  • 举报
 回复
[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]' 附近有语法错误。
还在加载中灬 2014-10-22
  • 打赏
  • 举报
 回复
),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) 试下这个
keanmanli 2014-10-22
  • 打赏
  • 举报
 回复
引用 15 楼 Tiger_Zhao 的回复:
ISNULL(t.分数,60) AS 分数
更改为 
ISNULL(Convert(varchar(11),t.分数),'?') AS 分数
你既然要求几万*上千的表格，除了乘积还能用什么方法？
新手不懂啊，不知道怎么实现，只是急需要数据做实验，然后需要改变数据格式
keanmanli 2014-10-22
  • 打赏
  • 举报
 回复
引用 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] 然后呢
Tiger_Zhao 2014-10-22
  • 打赏
  • 举报
 回复 
ISNULL(t.分数,60) AS 分数

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


你既然要求几万*上千的表格，除了乘积还能用什么方法？
keanmanli 2014-10-22
  • 打赏
  • 举报
 回复
引用 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
对于学生没选的课程的成绩设置为？，怎么处理呢？然后我的数据库表学生有几万个，课程也有上千个，这样枚举的话不合适吧，那这样该怎么处理呢
Tiger_Zhao 2014-10-22
  • 打赏
  • 举报
 回复 
-- 用临时表做的测试数据

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
还在加载中灬 2014-10-22
  • 打赏
  • 举报
 回复
引用 10 楼 keanmanli 的回复:
[quote=引用 8 楼 ky_min 的回复:] 动态的话，你有课程表吗?记录有哪些课程 如果没有也可以~~
有课程表，但是课程有几百个，不能枚举到case吧，怎么解决呢[/quote] 你试下，如#11动态处理
还在加载中灬 2014-10-22
  • 打赏
  • 举报
 回复 
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)
keanmanli 2014-10-22
  • 打赏
  • 举报
 回复
引用 8 楼 ky_min 的回复:
动态的话，你有课程表吗?记录有哪些课程 如果没有也可以~~
有课程表，但是课程有几百个，不能枚举到case吧，怎么解决呢
gqqcookle 2014-10-22
  • 打赏
  • 举报
 回复
引用 6 楼 keanmanli 的回复:
[quote=引用 4 楼 gqqcookle 的回复:]
课程不固定呢，毕竟如果课程多的话，枚举也太麻烦了，还有学生选的课程中没成绩的设为60，学生没选的课程的成绩设为？，这样的怎么实现呢，都怪我没描述清楚[/quote]
还在加载中灬 2014-10-22
  • 打赏
  • 举报
 回复
动态的话，你有课程表吗?记录有哪些课程 如果没有也可以~~
还在加载中灬 2014-10-22
  • 打赏
  • 举报
 回复
引用 5 楼 keanmanli 的回复:
[quote=引用 2 楼 ky_min 的回复:] 你参考一下 
SELECT P.* FROM [学生成绩表]PIVOT(MAX([分数])FOR[课程]IN([语文],[数学],[物理],[英语],[德语]))P
如果课程不固定，还可以用动态处理
动态怎么处理呢，不同人选的课程不一样，有的学生课程成绩还为空，就设为60，没选的课程成绩设置为？[/quote] 

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 [姓名]
keanmanli 2014-10-22
  • 打赏
  • 举报
 回复
引用 4 楼 gqqcookle 的回复:
课程不固定呢，毕竟如果课程多的话，枚举也太麻烦了，还有学生选的课程中没成绩的设为60，学生没选的课程的成绩设为？，这样的怎么实现呢，都怪我没描述清楚
keanmanli 2014-10-22
  • 打赏
  • 举报
 回复
引用 2 楼 ky_min 的回复:
你参考一下 
SELECT P.* FROM [学生成绩表]PIVOT(MAX([分数])FOR[课程]IN([语文],[数学],[物理],[英语],[德语]))P
如果课程不固定，还可以用动态处理
动态怎么处理呢，不同人选的课程不一样，有的学生课程成绩还为空，就设为60，没选的课程成绩设置为？
gqqcookle 2014-10-22
  • 打赏
  • 举报
 回复
加载更多回复（10）
sql多行转列
主要是关于sqlserver的多行转列的问题，这是我经过个人测试的语句。
DB2 SQL 实现行转列，列转行
DB2 SQL 通过函数(CONCAT/POSSTR/LOCATE)实现行转列，列转行 可以按照标点把多列转换为一行，多行转换为一列
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题：1. 自己写了SQL却不知道对不对。2. 在上线后发现原有的数据内容SQL语句执⾏结果没问题， 但是数据变化了以后SQL返回的结果就错误了。3. 稍微复杂⼀点的SQL就不知道该如何写了。4. 看不懂同⾏写的SQL是为什么。5. 不知道索引的原理。6. 不知道怎么加索引能够提⾼查询性能纵观全网的教程都只停留在概念解析的层面，没有深入剖析原理，所以看了那么多的教程，可还是不会写。本教程与其他教程不同带你深入了解原理，让你真正了解什么是SQL语句，让你懂得SQL语句的编写⽅法。不再为SQL语句而烦恼。真正做到面对任何问题能够判断出SQL语句是不是能够实现，该怎样实现。 
sql 多行合并某一列字符串拼接的示例
sql 多行合并某一列字符串拼接的示例语句
SQL SERVER 根据关键字符,将一列分成多行
可根据关键字符将一列分成多行显示,例如 A B 1 1.1.2 拆分成 1 1 1 1 1 2 进行相关的显示
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章