Oracle列转行、多列转成一行_oracle列转行listagg

列转行实验：

create table score2(

name varchar2(10),

Language number(3),

Math number(3),

English number(3)

);

insert into

score2 values('Zhang',80,67,95);

insert into score2 values('Li',79,84,62);

insert into score2(name,Language) values('Chen',88);

commit;

SELECT name,

'Language' subject,Language grade FROM score2

UNION ALL

SELECT name, 'Math' subject,Math grade FROM score2

UNION ALL

SELECT name, 'English' subject,English grade FROM score2

order by name;

多列转成一行 实验：

drop table t purge;

create table t(

dname varchar2(10),

ename varchar2(10)

);

insert into t values('sales','Scott');

insert into t values('sales','Jimes');

insert into t values('sales','Jack');

insert into t values('account','John');

insert into t values('account','Marry');

insert into t values('hr','Smith');

commit;

方法一：

select dname,WMSYS.WM_CONCAT(ename) ename from t group by dname;

方法二：

SELECT dname, SUBSTR(MAX (SYS_CONNECT_BY_PATH (ename, ',')), 2) NAME

FROM (

SELECT dname, ename, rn, LEAD (rn) OVER (PARTITION BY dname ORDER BY rn) rn1

FROM (SELECT dname, ename, ROW_NUMBER () OVER (ORDER BY ename) rn

FROM t)

)

START WITH rn1 IS NULL

CONNECT BY rn1 = PRIOR rn

GROUP BY dname;