189 8069 5689
oracle数据多行不同列进行合并显示
10年积累的网站设计制作、网站建设经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先网站制作后付款的网站建设流程，更有青州免费网站建设让你可以放心的选择与我们合作。
select id ,listagg( name, ',' ) within group ( order by id ) as name from TABLE_NAME GROUP BY id;
方法有如下两种：
1、利用存储过程，先查出所要的数据，循环放入一列中：
select 编码,decode(一级,null,null,一级||'')||decode(二级,null,null,二级||'')||decode(三级,null,null,三级||'')||decode(四级,null,null,四级||'') from 表名
2、使用wm_concat()方法，如select wm_concat(name) as name from user;
select name,
max(case when course = '语文' then course else null end) course1,
max(case when course = '语文' then score else null end) score1,
max(case when course = '数学' then course else null end) course2,
max(case when course = '数学' then score else null end) score2,
max(case when course = '英语' then course else null end) course3,
max(case when course = '英语' then score else null end) score3
from table
group by name
原表名字：test
三个字段：姓名：nm，选修课：xx，成绩：cj
分两张情况：
一、选修科目数量确定为2：
两种写法：
1、普通写法
with t as
(select nm,
'选修' || row_number() over(partition by nm order by cj) xx_tp,
'成绩' || row_number() over(partition by nm order by cj) cj_tp,
xx,
cj
from test a)
select nm "姓名",
max(decode(xx_tp, '选修1', xx, null)) "选修1",
max(decode(cj_tp, '成绩1', cj, null)) "成绩1",
max(decode(xx_tp, '选修2', xx, null)) "选修2",
max(decode(cj_tp, '成绩2', cj, null)) "成绩2"
from t
group by nm
2、pivot
with t as
(select nm,
'选修' || row_number() over(partition by nm order by cj) xx_tp,
'成绩' || row_number() over(partition by nm order by cj) cj_tp,
xx,
cj
from test a)
select nm "姓名", max(xx1) "选修1", max(cj1) "成绩1", max(xx2) "选修2", max(cj2) "成绩2"
from (select *
from t
pivot(max(xx)
for xx_tp in('选修1' xx1, '选修2' xx2))) a
pivot (max(cj) for cj_tp in('成绩1' cj1, '成绩2' cj2))
group by nm
二、选修科目数量不确定
首先去 ;tid=1609939extra=highlight=%B6%AF%CC%AC%D0%D0%D7%AA%C1%D0page=1 复制动态行转列的代码到sql窗口中运行，然后执行如下代码：
with t as
(select *
from table(pivot('select nm,
''成绩'' || row_number() over(partition by nm order by cj) cj_tp,
cj
from test a'))),
t1 as
(select *
from table(pivot('select nm,
''选修'' || row_number() over(partition by nm order by cj) cj_tp,
xx
from test a')))
select * from t, t1 where t.nm = t1.nm
以上。