ORACLE字符串转换列

create table test 
(  id number not null enable, 
   num number not null enable, 
   name varchar2(20) 
);




insert into test values (123,1,'apple'); 
insert into test values (123,1,'banana'); 
insert into test values (123,1,'mango'); 
insert into test values (123,2,'dog'); 
insert into test values (124,1,'grape'); 
insert into test values (124,2,'cat'); 
insert into test values (124,2,'dolphin');
insert into test values (125,1,'orange');
insert into test values (125,3,'usa');


select * from test;




 select id,
        max(decode(num, 1, zhi)) Z1,
max(decode(num, 2, zhi)) Z2,
max(decode(num, 3, zhi)) Z3,
max(decode(num, 4, zhi)) Z4
from (select id, num, substr(max(sys_connect_by_path(name, ', ')), 2) zhi
from (select t.*, row_number() over(partition by t.id, t.num order by t.name) rn from test t)
  start with rn = 1
connect by prior id = id
    and prior num = num
and prior rn = rn - 1  --prior rn：上一行的列,如果上一行的列值是5,那prior rn = 4, 那rn - 1 = 4
  group by id, num)
group by id




--sys_connect_by_path
/*


row_number()  顺序号码,  也就是 行号, 比如 1,2,3,4,5 这样的顺序。生产序号的方法通过over()函数里面的语句来控制


over()  语法需要，必须的。


partition by t.id, t.num  是按照 t.id, t.num  分区。


也就是说 如果有不同的 t.id, t.num  , 这个序号又重新从1开始计算。


order by t.name 是 排序方式， 也就是按最小的 t.name排序  


row_number()  是1，然后随着 t.name 的增加，  row_number() 不断递增。


解析：
  1. select t.*, row_number() over(partition by t.id, t.num order by t.name) rn from test t
     给test表增加一列, 按id, num分区, 以name排序, row_number() 是序列号
     
  2. sys_connect_by_path函数主要作用是把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。


  3. max(sys_connect_by_path(name, ', '))是获取列中显示最大的那一个字符串
  
  4. substr(max(sys_connect_by_path(name, ', ')), 2) 去掉前两位,显示后面的字符串


  5. prior rn = rn - 1  --prior rn：上一行的列值,如果rn是5,那prior rn = 4, 所以rn - 1 = 4




*/




--例2：
--  drop table test_ye;
create table test_ye
(  id        number,
   province  varchar2(20) ,
   city      varchar2(20) 
);




insert into test_ye values (1,'广东','广州');
insert into test_ye values (1,'广东','深圳');
insert into test_ye values (1,'广东','惠州');
insert into test_ye values (2,'湖南','长沙');
insert into test_ye values (2,'湖南','湘潭');


select * from test_ye;


select t2.province, substr(max(sys_connect_by_path(city, ', ')), 3) city
  from (select t1.*,
               row_number() over(partition by t1.id, t1.province order by city) rn
          from test_ye t1) t2
 start with rn = 1
connect by prior id = id
       and prior province = province
       and prior rn = rn - 1
 group by id, province;