victorsue66

oracle row Convert to Column

申請獎學金紀錄(YYYY table)
Stno apyyyy
0001 091
0002 092
0003 092
0001 093
0003 093
0004 093
0001 094

轉成
Stno apyyyy_list
001 091,092,093,094
002 092
003 092,093
004 094

SQL 隨記
select
Stno, substr(SYS_CONNECT_BY_PATH( apyyyy-1911, ',0'),2) apyyyy_list
from
(select c.apyyyy, c.Stno, count(*) OVER ( partition by c.Stno ) cnt,
ROW_NUMBER () OVER ( partition by c.Stno order by c.apyyyy) seq
from YYYY c
where c.Stno is not null)
where seq=cnt
start with seq=1
connect by prior seq+1=seq and prior Stno=Stno
參考 Oracle SQL displaying multiple columns per row