189 8069 5689
oracle中列传行可用wm_concat来实现。
创新互联拥有网站维护技术和项目管理团队，建立的售前、实施和售后服务体系，为客户提供定制化的做网站、成都网站设计、网站维护、资阳服务器托管解决方案。为客户网站安全和日常运维提供整体管家式外包优质服务。我们的网站维护服务覆盖集团企业、上市公司、外企网站、商城网站建设、政府网站等各类型客户群体，为全球近千家企业提供全方位网站维护、服务器维护解决方案。
如test表中数据如下：
现要将name列一列显示成行，可用如下语句：
select wm_concat(name) from test;
结果：
SQL create table t (a number, b varchar2(10));
表已创建。
SQL insert into t values(1,'A');
已创建 1 行。
SQL insert into t values(1,'B');
已创建 1 行。
SQL insert into t values(2,'A');
已创建 1 行。
SQL insert into t values(2,'B');
已创建 1 行。
SQL insert into t values(3,'C');
已创建 1 行。
SQL insert into t values(3,'F');
已创建 1 行。
SQL insert into t values(4,'D');
已创建 1 行。
SQL commit;
提交完成。
SQL select a,max(decode(c,1,b,null)),
2 max(decode(c,2,b,null)),
3 max(decode(c,3,b,null))
4 from(select a,b,row_number()over(partition by a order by b ) c from t)
5* group by a
SQL /
A MAX(DECODE MAX(DECODE MAX(DECODE
---------- ---------- ---------- ----------
1 A B
2 A B
3 C F
4 D
SQL
基本思路：对每班学生排序，根据序号构造列名，拼接动态sql
--测试数据
create table
("学生" varchar2(10)
,"学号" varchar2(10)
,"班级" varchar2(10)
);
insert into "表A"
select '张三','100','一班' from dual union all
select '李四','101','二班' from dual union all
select '王五','102','一班' from dual union all
select '赵六','103','三班' from dual union all
select '李二','104','二班' from dual
--动态拼接Pivot
declare
sqlstr varchar2(8000):='';
begin
--构造类似于 '学号1','学号2',... 的字符串
for x in (
select distinct row_number() over (partition by "班级" order by "学号") seq
from "表A" order by seq ) loop
sqlstr := sqlstr || ',''' || '学号' ||to_char(x.seq)||'''';
end loop;
sqlstr:=substr(sqlstr,2,length(sqlstr)-1);
--将前面构造的字符串放入Pivot语句中
sqlstr:='
select * from (
select "学号","班级", ''学号''|| to_char(
row_number() over (partition by "班级" order by "学号")) seq
from "表A") t
pivot(
max("学号")
for seq in ('||sqlstr||')
)';
--dbms_output.put_line(sqlstr);
--将查询结果放入临时视图中
sqlstr := 'CREATE OR REPLACE VIEW tmp_result AS '|| sqlstr;
--dbms_output.put_line(sqlstr);
execute immediate sqlstr;
end;
--查看结果
select * from tmp_result;
结果如下：
固定列数的行列转换如
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
语句如下：
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student
2、不定列行列转换如
c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
......
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
--用于返回值
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
Oracle 11g 行列互换 pivot 和 unpivot 说明在Oracle 11g中，Oracle 又增加了2个查询：pivot（行转列） 和unpivot（列转行）
参考：、 一下，网上有一篇比较详细的文档：
pivot 列转行
测试数据 (id，类型名称，销售数量)，案例：根据水果的类型查询出一条数据显示出每种类型的销售数量。
?
123456789
create table demo(id int,name varchar(20),nums int); ---- 创建表insert into demo values(1, '苹果', 1000);insert into demo values(2, '苹果', 2000);insert into demo values(3, '苹果', 4000);insert into demo values(4, '橘子', 5000);insert into demo values(5, '橘子', 3000);insert into demo values(6, '葡萄', 3500);insert into demo values(7, '芒果', 4200);insert into demo values(8, '芒果', 5500);
分组查询 （当然这是不符合查询一条数据的要求的）
?
1
select name, sum(nums) nums from demo group by name
行转列查询
?
1
select * from (select name, nums from demo) pivot (sum(nums) for name in ('苹果' 苹果, '橘子', '葡萄', '芒果'));
注意： pivot（聚合函数 for 列名 in（类型）） ，其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 select distinct code from customers
当然也可以不使用pivot函数，等同于下列语句，只是代码比较长，容易理解
?
12
select * from （select sum(nums) 苹果 from demo where name='苹果'）,（select sum(nums) 橘子 from demo where name='橘子'）, （select sum(nums) 葡萄 from demo where name='葡萄'）,（select sum(nums) 芒果 from demo where name='芒果'）;
unpivot 行转列
顾名思义就是将多列转换成1列中去
案例：现在有一个水果表，记录了4个季度的销售数量，现在要将每种水果的每个季度的销售情况用多行数据展示。
创建表和数据
?
1234567
create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int); insert into Fruit values(1,'苹果',1000,2000,3300,5000);insert into Fruit values(2,'橘子',3000,3000,3200,1500);insert into Fruit values(3,'香蕉',2500,3500,2200,2500);insert into Fruit values(4,'葡萄',1500,2500,1200,3500);select * from Fruit
列转行查询
?
1
select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )
注意： unpivot没有聚合函数，xiaoshou、jidu字段也是临时的变量
本文记录一下Oracle列转行函数在Oracle11的一些不兼容问题，vm_concat在一些业务场景是必须的。不过这个函数使用要谨慎，底层实现应该也是group by等等实现的，性能并不是特别好。这个函数在Oracle12C是没有的，在Oracle11是不太兼容的，Oracle10可以正常使用。最近开发场景遇到这个问题，可以写到了自定义列转行函数的办法去解决。但是这种办法并不一定适用所有的业务场景。
解决方案
通过自定义函数解决也是可以的，不过我并不是这样做的。 下面介绍一下我的解决方法。首先分析一下，Oracle19C不兼容vm_concat列转行函数，并不代表其它函数不兼容，或许可以找到其它代替的，通过找资料，发现了Oracle11提供的另外一个函数：listagg()函数 语法：listagg(参数，‘分隔符’) within group(order by 参数id)，验证可以实现在19C正常使用
例如：
to_char(wm_concat(t.busi_id))
可以改写成：
to_char(listagg(t.busi_id,',' ) within GROUP (order by (t.busi_id)))
SQL如:
SELECT listagg(t.busi_id, ',') within GROUP(order by (t.busi_id))
FROM sys_cdc_sync_record t
WHERE cdc_sql_type = 'INSERT_SELECT'