Oracle Row to Column

First, easy to use

--no more than one main number bound to multiple line outputs (like mobile fun online)

SELECT F.town_name Town District,
F.school_name School,
F.class_name class,
F.student_name student,
B.phone Main Number,
B.bindphone-bound Sub-number
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;

--a row of multi-column outputs after a turn
(using the Analysis function)
SELECT Town_name,
School_name,
Class_name,
Student_name,
Phone
MAX (DECODE (RN, 1, Bindphone)) "Affection number 1",
MAX (DECODE (RN, 2, Bindphone)) "Affection number 2",
MAX (DECODE (RN, 3, Bindphone)) "Affection number 3",
MAX (DECODE (RN, 4, Bindphone)) "Affection number 4",
MAX (DECODE (RN, 5, Bindphone)) "Affection number 5",
MAX (DECODE (RN, 6, Bindphone)) "Affection number 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;

(without the use of analytic functions)
Select T.user_id,t.name,
Max (decode (T.yw_name, ' Traffic pack ', ' yes ', ' no ') is open for traffic packets,
Max (decode (t.yw_ Name, ' Traffic pack ', dt,null)) Whether the traffic pack is open,
Max (case t.yw_name= ' snack pack ' and t.attr_value like '% $50 ' then ' 50 yuan snack ' end) whether 50 yuan snack, max (case time t.yw_name= ' snack pack ' and t.attr_value like '% 50 ' then DT end) $50 snack time,
Max (case when t.yw_name= ' snack pack ' and t . Attr_value like '% $100 ' then ' 100 yuan snack ' end ' is 100 yuan snack,
Max (case when t.yw_name= ' snack pack ' and t.attr_value like '% $100% ' the N DT End) "100-yuan Snack time",
Max (case time t.yw_name= ' snack pack ' and t.attr_value like '% $200 ' then ' 200 yuan snack ' end) is 200 Yuan snack,
Max ( Case when t.yw_name= ' snack pack ' and t.attr_value like '% 200 ' then DT End ' $200 snack time ',
Max (case t.yw_name= ' snack pack ' and T.A Ttr_value is null then ' unknown snack pack ' end) is unknown snack pack,
Max (case t.yw_name= ' snack pack ' and t.attr_value is null then DT end) "Unknown snack pack Time "
from Temp_liut L, Liut_temp5 t
where l.acc_nbr=t.user_id
Group by T.user_id,t.name;

Second, using Wmsys.wm_concat system internal function
By using the Wmsys system user's Wm_concat function can also achieve row and column conversion effect (a list of display, data separated by commas)

--a teacher will take multiple classes
SELECT Town.name Town District,
S.school_name,
T.username Teacher,
T.userid account,
T.mphone Contact information,
R.name role,
Wmsys.wm_concat (C.class_name) class
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

Three, 11.2 new function Listagg
Syntax: Listagg (split column, delimiter) within group (order by row sequence)
Select Listagg (O.rybs, '; ') within group (order by O.rybs)
From Gk_xszrr o
where rownum <= 100;

Oracle Row to Column