SQL语句行列转换

有问又问

于 2014-12-12 10:54:13 发布

阅读量436

点赞数

分类专栏：基础知识

本文链接：https://blog.csdn.net/youwenyouwen/article/details/41891091

版权

基础知识专栏收录该内容

22 篇文章 0 订阅

订阅专栏

已知：表 A：   P_ID    Num    S_ID
               1         10       2
               1         5         1
               2         2         3
               3         4         3
得到的数据是这样的：
          PID     S1_num   S2_num   S3_num
          1           5            10             0
          2           0            0               2
          3           0            0               4

查询语句为：

SELECT P_ID,
       MAX(CASE WHEN S_ID=1 THEN Num ELSE 0 END) AS S1_num,
       MAX(CASE WHEN S_ID=2 THEN Num ELSE 0 END) AS S2_num,
       MAX(CASE WHEN S_ID=3 THEN Num ELSE 0 END) AS S3_num
FROM T
GROUP BY P_ID

select P_ID,SUM(t.S1_num) as S1_num ,
SUM(t.S2_num) as S2_num ,
SUM(t.S3_num)as S3_num
from ( select  P_ID, case  when  S_ID=1 then  Num  else 0 end as 'S1_num' ,
         case  when S_ID=2 then    Num  else 0  end as 'S2_num' ,
         case  when S_ID=3 then   Num else 0  end as 'S3_num'  from helpother )
as t group by t.P_ID