SQL行转列、列转行
前言
开发中我们经常会用到行转列,这里记录一下我在项目中实现行转列的思路。需求:报表模块,统计某机房机架的不同状态(1 空闲 2 预占 3 占用)的数量(真实需求更为复杂,这里只是讨论技术,简化一下)
decode函数
以下介绍摘自百度百科:
DECODE函数是ORACLE PL/SQL的功能强大的函数之一,以其简洁的运算方式,可控的数据模型和灵活的格式转换而闻名。目前还只有ORACLE公司的SQL提供了此函数,其它数据库厂商的SQL实现还没有此功能。
在DECODE的语法中,实际上就是这样的逻辑处理过程。它的语法如下:
DECODE(value, if1, then1, if2,then2, if3,then3, . . . ,else )
Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试,如果value的值为if1,Decode 函数的结果是then1;如果value等于if2,Decode函数结果是then2;等等。事实上,可以给出多个if/then 配对。如果value结果不等于给出的任何配对时,Decode 结果就返回else 。
编写SQL
res_rack机架表,先按状态分组,查询出状态值、已经对应的数量,此时是行数据
select ra.service_status status, count(ra.id) counts --先查询出行数据,状态值对应的数量 from res_rack ra --机架表 where ra.roomid = '2' --所属机房 group by ra.service_status
再利用decode跟sum函数进行值的判断跟统计,完成行转列
select sum(decode(t.status, '1', t.counts, 0)) idle, -- 1 为空闲 sum(decode(t.status, '2', t.counts, 0)) reserved, -- 2 为预占 sum(decode(t.status, '3', t.counts, 0)) occupied -- 1 为占用 from (select ra.service_status status, count(ra.id) counts --先查询出行数据,状态值对应的数量 from res_rack ra --机架表 where ra.roomid = '2' --所属机房 group by ra.service_status) t --按照状态值分组
更新补充
2019-07-29补充:MySql是IF函数,用法跟Oracle的DECODE函数一样
2021-06-16更新,oracle 行转列(PIVOT)与列转行(UNPIVOT),参考:https://www.cnblogs.com/mellowsmile/p/4642306.html
在Oracle 11g中,Oracle 又增加了2个查询:pivot(行转列) 和unpivot(列转行)
PS:表字段要大写,不然会报错
行转列
-- 行转列(PIVOT) create table qch_test1(name varchar(20),nums int); insert into qch_test1 values('苹果', 1000); insert into qch_test1 values('苹果', 2000); insert into qch_test1 values('苹果', 4000); insert into qch_test1 values('橘子', 5000); insert into qch_test1 values('橘子', 3000); insert into qch_test1 values('葡萄', 3500);
select * from qch_test1; select * from qch_test1 pivot (sum(nums) for name in ('苹果', '橘子','葡萄'));
列转行
-- 列转行(UNPIVOT) create table qch_test2(Q1 int, Q2 int, Q3 int, Q4 int); insert into qch_test2 values(1000,2000,3300,5000); select * from qch_test2; select jidu, xiaoshou from qch_test2 unpivot (xiaoshou for jidu in (Q1, Q2, Q3, Q4))
2021-06-30更新,GaussDB、PostgreSql 列转行,参考:https://blog.csdn.net/qq_35377323/article/details/115178631
-- 列转行 create table qch_test2(Q1 int, Q2 int, Q3 int, Q4 int); insert into qch_test2 values(1000,2000,3300,5000); select * from qch_test2; SELECT split_part( UNNEST ( string_to_array( mengestr, ',' ) ), '~', '1' ) AS jidu, split_part( UNNEST ( string_to_array( mengestr, ',' ) ), '~', '2' ) AS xiaoshou FROM ( SELECT 'Q1' || '~' || Q1 || ',' || 'Q2' || '~' || Q2 || ',' || 'Q3' || '~' || Q3 || ',' || 'Q4' || '~' || Q4 AS mengestr FROM qch_test2 )
PS:如果拼接的列太多(比如拼接了60个列)会报错,这时候可以拆成多个字段,在外面再套一层查询
版权声明
作者:huanzi-qch
若标题中有“转载”字样,则本文版权归原作者所有。若无转载字样,本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接,否则保留追究法律责任的权利.
捐献、打赏
请注意:相应的资金支持能更好的持续开源和创作,如果喜欢这篇文章,请随意打赏!
![](http://huanzi-qch.gitee.io/file-server/images/zhifubao.png)
支付宝
![](http://huanzi-qch.gitee.io/file-server/images/weixin.png)
微信
QQ群交流群
QQ群交流群
有事请加群,有问题进群大家一起交流!
有事请加群,有问题进群大家一起交流!
![](http://huanzi-qch.gitee.io/file-server/images/qq.png)