一、簡易運用

——>沒轉之前一個主號綁定多個副號的多行輸出（像移動的歡樂在線）

SELECT?? f.town_name 鎮區,
????????? f.school_name 學校,
????????? f.class_name 班級,
????????? f.student_name 學生,
????????? b.phone 主號碼,
????????? b.bindphone 綁定的副號碼
?? FROM?? dg_FPS_BINDPHONE b, dg_family f
WHERE?????? b.family_id = f.family_id
????????? AND b.phone = f.phone
????????? AND f.school_id = 61758
????????? AND NVL (f.is_test, 0) <> 1;

——>轉之后的一行多列輸出
（使用分析函數的情況）
?? SELECT?? town_name,
??????????? school_name,
??????????? class_name,
??????????? student_name,
??????????? phone,
??????????? MAX (DECODE (rn, 1, bindphone)) "親情號碼1",
??????????? MAX (DECODE (rn, 2, bindphone)) "親情號碼2",
??????????? MAX (DECODE (rn, 3, bindphone)) "親情號碼3",
??????????? MAX (DECODE (rn, 4, bindphone)) "親情號碼4",
??????????? MAX (DECODE (rn, 5, bindphone)) "親情號碼5",
??????????? MAX (DECODE (rn, 6, bindphone)) "親情號碼6"
???? FROM?? (SELECT?? f.town_name,
????????????????????? f.school_name,
????????????????????? f.class_name,
????????????????????? f.student_name,
????????????????????? b.phone,
????????????????????? b.bindphone,
????????????????????? COUNT( * )
???????????????????????? OVER ( PARTITION BY f.town_name,f.school_name,f.class_name,f.student_name,b.phone
??????????????????????????? ORDER BY ROWNUM
??????????????????????????? ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
???????????????????????? )
???????????????????????? rn
?????????????? FROM?? dg_fps_bindphone b, dg_family f
????????????? WHERE?????? b.family_id = f.family_id? AND? b.phone = f.phone? AND? f.school_id = 61758? AND? NVL (f.is_test, 0) <> 1)
GROUP BY?? town_name,school_name,class_name,student_name,phone;

(不使用分析函數的情況)
select t.user_id,t.name,
max(decode(t.yw_name,'流量包','是','否')) 是否開通流量包,
max(decode(t.yw_name,'流量包',dt,NULL)) 是否開通流量包,
max(case when t.yw_name='加餐包' and t.attr_value like '%50元%' then '50元加餐' end) 是否50元加餐,
max(case when t.yw_name='加餐包' and t.attr_value like '%50元%' then dt end) 五十元加餐時間,
max(case when t.yw_name='加餐包' and t.attr_value like '%100元%' then '100元加餐' end) 是否100元加餐,
max(case when t.yw_name='加餐包' and t.attr_value like '%100元%' then dt end) "100元加餐時間",
max(case when t.yw_name='加餐包' and t.attr_value like '%200元%' then '200元加餐' end) 是否200元加餐,
max(case when t.yw_name='加餐包' and t.attr_value like '%200元%' then dt end) "200元加餐時間",
max(case when t.yw_name='加餐包' and t.attr_value is null then '未知加餐包' end) 是否未知加餐包,
max(case when t.yw_name='加餐包' and t.attr_value is null then dt end) "未知加餐包時間"
from temp_liut l, liut_temp5 t
where l.acc_nbr=t.user_id
group by t.user_id,t.name;

二、用 wmsys.wm_concat 系統內部函數
???? 通過運用wmsys系統用戶下的wm_concat函數也能達到行列轉換效果（一列顯示，數據之間以逗號分隔）

--? 一個老師會帶多個班級
?? SELECT?? town.name 鎮區,
??????????? s.school_name,
??????????? t.username 教師,
??????????? t.userid 賬號,
??????????? t.mphone 聯系方式,
??????????? r.name 角色,
??????????? wmsys.wm_concat (c.class_name) 班級
???? FROM?? area a,
??????????? town,
??????????? qx_sch_role r,xj_school s LEFT JOIN xj_teacher t ON s.id = t.school_id
?????????????? LEFT JOIN tea_class_subject su ON t.id = su.teacher_id
??????????? LEFT JOIN xj_class c ON su.class_id = c.id
??? WHERE?????? a.id = town.area_id
??????????? AND town.id = s.town_id
??????????? AND r.id = t.role_id
??????????? AND a.id = 1
??????????? AND NVL (town.is_test, 0) <> 1
GROUP BY?? town.name,s.school_name,t.username,t.userid,t.mphone,r.name

三、11.2新函數listagg
語法：listagg(分割列，分隔符) within group(order by 排序列)
select listagg(o.rybs, ';') within group(order by o.rybs)
?? from gk_xszrr o
? where rownum <= 100;

轉載于:https://www.cnblogs.com/bbliutao/p/7575707.html

總結

以上是生活随笔為你收集整理的Oracle 行转列的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。