DB2行转成列例子

 create table tb_list(
   id int not null,
   cname varchar(20)
   );

insert into tb_list values(1,'张三');
insert into tb_list values(2,'李四');
insert into tb_list values(3,'王五');
insert into tb_list values(4,'赵六');
insert into tb_list values(5,'赵六');
insert into tb_list values(1,'陈七');

with cname1(cname,p_rownum,n_rownum) as (
      select cname,rownumber() over(order by id) as p_rownum,rownumber() over(order by id)+1 as n_rownum
       from tb_list
    ),
   cname2(cname,p_rownum,n_rownum) as (
     select cast(t1.cname as varchar(1000)),p_rownum,n_rownum
      from cname1 t1
      where p_rownum=1
     union all
     select t1.cname||'|'||t2.cname,t2.n_rownum,t2.n_rownum+1
      from cname1 t1,cname2 t2
      where t1.p_rownum=t2.n_rownum
    )
   select cname
     from cname2 a
     where n_rownum=(select max(n_rownum) from cname1);

 

最有一句是DB2标准的递归写法。