1.固定列数
2.不定列数
如果Oracle版本是11GR2,那么可以使用listagg函数,可以方便很多。
- create table t1
- (
- studentno int,
- subject varchar2(10),
- grade int
- );
-
- insert into t1 values(1,'语文',80);
- insert into t1 values(1,'数学',82);
- insert into t1 values(1,'英语',84);
- insert into t1 values(2,'语文',70);
- insert into t1 values(2,'数学',74);
- insert into t1 values(2,'英语',76);
- insert into t1 values(3,'语文',90);
- insert into t1 values(3,'数学',93);
- insert into t1 values(3,'英语',94);
- commit;
-
- SQL> select * from t1;
STUDENTNO SUBJECT GRADE
---------- ---------- ----------
1 语文 80
1 数学 82
1 英语 84
2 语文 70
2 数学 74
2 英语 76
3 语文 90
3 数学 93
3 英语 94
已选择9行。
- select studentno 学号,
sum(decode(subject,'语文',grade,null)) 语文,
sum(decode(subject,'数学',grade,null)) 数学,
sum(decode(subject,'英语',grade,null)) 英语
from t1 group by studentno;
学号 语文 数学 英语 - ----- ---------- ---------- ----------
1 80 82 84
2 70 74 76
3 90 93 94
- select studentno,
(select grade from t1 v2 where v2.studentno=t1.studentno and v2.subject='语文') 语文,
(select grade from t1 v2 where v2.studentno=t1.studentno and v2.subject='数学') 数学,
(select grade from t1 v2 where v2.studentno=t1.studentno and v2.subject='英语') 英语
from t1 group by studentno;
- create table t2
- (
- key int,
- value varchar2(10)
- );
-
- insert into t2 values(1,'我');
- insert into t2 values(1,'是');
- insert into t2 values(1,'谁');
- insert into t2 values(2,'知');
- insert into t2 values(2,'道');
- insert into t2 values(3,'不');
- commit;
- SQL> select * from t2;
-
- KEY VALUE
- ---------- ----------
- 1 我
- 1 是
- 1 谁
- 2 知
- 2 道
- 3 不
-
- 已选择6行。
-
- with v1 as
(
select key,value,row_number() over(partition by key order by key) r from t2
),
v2 as
(
select max(sys_connect_by_path(value,' ')) result from v1 start with r=1 connect by r=prior r+1 and key=prior key group by key
)
select * from v2; -
RESULT
------------------
我 是 谁
知 道
不
- with v1 as
- (
- select key,value,row_number() over(partition by key order by key) r from t2
- ),
- v2 as
- (
- select listagg(value,',') within group (order by r) result from v1 group by key
- )
- select * from v2;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-775660/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-775660/