使用case when,union all实现sql行转列、列转行

青旅半醒 2022-03-31 07:20 783阅读 0赞

转自：[https://blog.csdn.net/weixin\_38653290/article/details/85488532][https_blog.csdn.net_weixin_38653290_article_details_85488532]

今天分享下使用case when,union all实现sql行转列、列转行

-- 建表 
    CREATE TABLE StudentScores ( 
    UserName NVARCHAR(20), -- 学生姓名 
    Subject NVARCHAR(30), -- 科目 
    Score FLOAT -- 成绩 )

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTgwNDA0OQ_size_16_color_FFFFFF_t_70][]

-- 添加数据 
    INSERT INTO StudentScores SELECT '张三', '语文', 80 ; 
    INSERT INTO StudentScores SELECT '张三', '数学', 90 ; 
    INSERT INTO StudentScores SELECT '张三', '英语', 70 ; 
    INSERT INTO StudentScores SELECT '张三', '生物', 85 ; 
    INSERT INTO StudentScores SELECT '李四', '语文', 80 ; 
    INSERT INTO StudentScores SELECT '李四', '数学', 92 ; 
    INSERT INTO StudentScores SELECT '李四', '英语', 76 ; 
    INSERT INTO StudentScores SELECT '李四', '生物', 88 ; 
    INSERT INTO StudentScores SELECT '码农', '语文', 60 ; 
    INSERT INTO StudentScores SELECT '码农', '数学', 82 ; 
    INSERT INTO StudentScores SELECT '码农', '英语', 96 ; 
    INSERT INTO StudentScores SELECT '码农', '生物', 78 ;

-- 使用PIVOT行转列 
    SELECT * FROM StudentScores 
    AS P PIVOT ( SUM(Score) FOR p.Subject IN ('语文','数学','英语','生物') ) AS T 
    
    -- 使用case when行转列 
    select UserName, 
    max(case when subject='语文' then score else 0 end) 语文, 
    max(case when subject='数学' then score else 0 end) 数学,
     max(case when subject='英语' then score else 0 end) 英语, 
    max(case when subject='生物' then score else 0 end) 生物 
    from StudentScores group by UserName

![20190102165536505.png][]

-- 建表 drop table if exists StudentScores2; 
    CREATE TABLE StudentScores2 ( 
    UserName NVARCHAR(20), -- 学生姓名 
    语文 FLOAT, -- 科目 
    数学 FLOAT, -- 科目 
    英语 FLOAT, -- 科目 
    生物 FLOAT -- 科目 ); 
    
    -- 添加数据 
    insert into StudentScores2 select UserName, 
    max(case when subject='语文' then score else 0 end) 语文, 
    max(case when subject='数学' then score else 0 end) 数学, 
    max(case when subject='英语' then score else 0 end) 英语, 
    max(case when subject='生物' then score else 0 end) 生物 
    from StudentScores group by UserName;

![20190102165707209.png][]

-- 使用union all 列转行
    
    select UserName,'语文' subject,语文 score from StudentScores2 union all 
    select UserName,'数学' subject,数学 score from StudentScores2 union all 
    select UserName,'英语' subject,英语 score from StudentScores2 union all 
    select UserName,'生物' subject,生物 score from StudentScores2;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTgwNDA0OQ_size_16_color_FFFFFF_t_70 1][]

[https_blog.csdn.net_weixin_38653290_article_details_85488532]: https://blog.csdn.net/weixin_38653290/article/details/85488532
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTgwNDA0OQ_size_16_color_FFFFFF_t_70]: /images/20220331/c65498dbef2c4238be33d044fea28c93.png
[20190102165536505.png]: /images/20220331/11043bb9df5d450c9355780095a23fc7.png
[20190102165707209.png]: /images/20220331/daddccd94e5e4deca0e1efd0546681bc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTgwNDA0OQ_size_16_color_FFFFFF_t_70 1]: /images/20220331/beba6dc97c314172bec767575500240b.png