Oracle 行转列，列转行 11.2.0.4_11.2.0.4 行转列函数-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_41907418/article/details/109004355

字段拼接成字符串

SELECT userid||username FROM manager;
SELECT concat(userid,username) FROM manager;

字符串转多行

SELECT '1' FROM dual union all SELECT '2' FROM dual;

列转行 wm_concat()

drop table test;
create table test(id number,username varchar2(20));
insert into test values(1,'a');
insert into test values(1,'b');
insert into test values(1,'c');
insert into test values(2,'d');
insert into test values(2,'e');
commit;

--wm_concat函数默认是CLOB字段，当超过限制时，会报错ORA-22816:操作数值超出系统的限制

select wm_concat(username) from test;--列转行，默认用,分隔
--<CLOB>
select TO_CHAR(wm_concat(username)) from test; --转为varchar
--a,b,c,d,e

select dbms_lob.substr(replace(wm_concat(username), ',', '|'))
  from test;--将,替换为|
--a|b|c|d|e

--实际上处理CLOB字段的时候，直接TO_CHAR，当长度超过4000的时候，会报错，提示列被截取；
select dbms_lob.substr(wm_concat(username), 4000) from test; --可以使用dbms_lob.substr(clobcolumn,4000)，对CLOB字段进行截取；截取的长度是4000还是2000根据存储的是汉字和数据决定长度；
select substr(wm_concat(username),0,4000) from test; --直接使用SUBSTR对CLOB字段进行截取，是不能起到任何作用的

SELECT id, dbms_lob.substr(wm_concat(username))
  FROM test
 group by id;--按userid分组合并name
1	1	a,c,b
2	2	d,e
;

--扩展，拼接表字段
select * from user_tab_columns;
select dbms_lob.substr('create or replace view as select ' ||
                       wm_concat(column_name) || ' from TEST',
                       4000) sqlStr
  from user_tab_columns
 where table_name = 'TEST';

行转列 pivot()

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);
commit;

SELECT * FROM demo;
1 1 苹果  1000
2 2 苹果  2000
3 3 苹果  4000
4 4 橘子  5000
5 5 橘子  3000
6 6 葡萄  3500
7 7 芒果  4200
8 8 芒果  5500
;
select name, sum(nums) nums from demo group by name;
select * from (select name, nums from demo) pivot (sum(nums) for name in ('苹果', '橘子', '葡萄', '芒果'));

SELECT * FROM (select name, nums from demo) pivot (sum(nums) for name in (
--SELECT wm_concat(distinct(name)) FROM demo -- ORA-00936:缺失表达式
--SELECT distinct name FROM demo -- ORA-00936:缺失表达式
'苹果', '橘子', '葡萄', '芒果'
));
SELECT * FROM (select name, nums from demo) pivot xml (sum(nums) for name in (
SELECT distinct name FROM demo 
));--A subquery is used only in conjunction with the XML keyword 子查询只能与关键字xml使用
--<XMLTYPE>
<?xml version="1.0"?>
<PivotSet>
<item>
<column name="NAME">橘子</column>
<column name="SUM(NUMS)">8000</column>
</item>
<item>
<column name="NAME">芒果</column>
<column name="SUM(NUMS)">9700</column>
</item>
<item>
<column name="NAME">苹果</column>
<column name="SUM(NUMS)">7000</column>
</item>
<item>
<column name="NAME">葡萄</column>
<column name="SUM(NUMS)">3500</column>
</item>
</PivotSet>
;
pivot 行转列（聚合函数 for 列名 in（类型）） ，其中 in('') 中可以指定别名，in中还可以指定子查询
pivot 会隐式执行 group by
当然也可以不使用pivot函数，等同于下列语句，只是代码比较长，容易理解
;
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()

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);
commit;
select * from Fruit;
1 1 苹果  1000  2000  3300  5000
2 2 橘子  3000  3000  3200  1500
3 3 香蕉  2500  3500  2200  2500
4 4 葡萄  1500  2500  1200  3500
;
select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) );
1 1 苹果  Q1  1000
2 1 苹果  Q2  2000
3 1 苹果  Q3  3300
4 1 苹果  Q4  5000
5 2 橘子  Q1  3000
6 2 橘子  Q2  3000
7 2 橘子  Q3  3200
8 2 橘子  Q4  1500
9 3 香蕉  Q1  2500
10  3 香蕉  Q2  3500
11  3 香蕉  Q3  2200
12  3 香蕉  Q4  2500
13  4 葡萄  Q1  1500
14  4 葡萄  Q2  2500
15  4 葡萄  Q3  1200
16  4 葡萄  Q4  3500
;