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博客 ” ，链接：https://blog.itpub.net/29254281/viewspace-775660/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
JAVA打印有序矩阵
下一篇：
计算两个日期之间所有的工作日
请登录后发表评论
登录
全部评论
