oracle 行转列举例

1. 数据准备

create table  bruce_test

(
     a  int ,
     b  int ,
     c   int ,
     d int 


)


insert into bruce_test values(1,1,3,4);
insert into bruce_test values(1,12,3,5);
insert into bruce_test values(1,13,3,6);
insert into bruce_test values(1,14,3,7);
insert into bruce_test values(1,5,3,8);
insert into bruce_test values(2,1,3,4);
insert into bruce_test values(2,12,3,5);
insert into bruce_test values(2,3,3,6);
insert into bruce_test values(2,4,3,6);
insert into bruce_test values(3,5,3,7);
insert into bruce_test values(4,6,3,8);
commit;






SELECT a, sum(d) as num,
MAX(decode(rn, 1, b||'-'||d, NULL)) ||
MAX(decode(rn, 2, ','||b||'-'||d, NULL)) ||
MAX(decode(rn, 3, ','||b||'-'||d, NULL))  as str
FROM 
(
SELECT a,
b,d,
row_number() over(PARTITION BY a ORDER BY d desc) AS rn
FROM bruce_test
) t
GROUP BY a

ORDER BY 1;

2  抓图