列转行

create table scott.test nologging
as
select 1111 as id, 'aaaa' as text from dual
union all
select 1111 as id, 'bbbb' as text from dual
union all
select 2222 as id, 'xxxx' as text from dual
union all
select 1111 as id, 'eeee' as text from dual
union all
select 3333 as id, 'mmmm' as text from dual
union all
select 2222 as id, 'kkkk' as text from dual
union all
select 8888 as id, 'wwww' as text from dual
union all
select 8888 as id, 'bbbb' as text from dual
union all
select 8888 as id, 'oooo' as text from dual
union all
select 1111 as id, 'llll' as text from dual
;
select * from scott.test order by 1,2;
        ID TEXT
---------- ----
      1111 aaaa
      1111 bbbb
      1111 eeee
      1111 llll
      2222 kkkk
      2222 xxxx
      3333 mmmm
      8888 bbbb
      8888 oooo
      8888 wwww

select id,text,
       row_number() over(partition by id order by id) as rn,
       count(1)     over(partition by id order by id) as cnt
from  (select * from scott.test order by 1,2)
;
        ID TEXT         RN        CNT
---------- ---- ---------- ----------
      1111 aaaa          1          4
      1111 bbbb          2          4
      1111 eeee          3          4
      1111 llll          4          4
      2222 kkkk          1          2
      2222 xxxx          2          2
      3333 mmmm          1          1
      8888 bbbb          1          3
      8888 oooo          2          3
      8888 wwww          3          3

col texts format a64
select id,
       substr(sys_connect_by_path(text,','),2) as texts
from (select id,text,
             row_number() over(partition by id order by id) as rn,
             count(1)     over(partition by id order by id) as cnt
      from  (select * from scott.test order by 1,2)
      )
where level=cnt
start with rn=1
connect by prior id=id and prior rn=rn-1;
        ID TEXTS
---------- ----------------------------------------------------------------
      1111 aaaa,bbbb,eeee,llll
      2222 kkkk,xxxx
      3333 mmmm
      8888 bbbb,oooo,wwww

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21126685/viewspace-666775/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21126685/viewspace-666775/