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标准的递归写法。