Oracle 行转列 - 度量快速开发平台-专业、快速的软件定制快开平台

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;

--  一个老师会带多个班级
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