关于使用Oracle行转列的随笔
参考博客：Oracle 行列转换详解yuncode.net/code/c_59bf…
- 需要将数据库中的查询结果，导出为指定格式（数据列排版）Excel文件
- 实现方法
-
第一步
连表查询得到需要获取的目标数据，如图：观察查询结果可知，每个项目对应的得分项是一样的，因此可以根据每个得分项的编号seq_no作为行转列的条件，需要注意的是：排序条件要保证同每个项目的seq_no的排序顺序要保持一致，每个项目之间不能乱入哦，另外要注意group by，把需要查询的字段都加上。
然后用sum(decode())做为行转列的手段，sql如图：至此，我们是将每个项目进行行转列，但是每个项目最多有五条记录（可以理解为一张试卷有五个阅卷老师进行评分），下一步就是将每个项目中的五条记录也进行行转列。但是在进行行转列的时候需要有共同的规律，类似上述的seq_no，所以需要借助外力“ oracle为相同行记录增加序号列(row_number()函数)”，可见“yuncode.net/code/c_59bf…”，为每个项目加上序号，如图：
有了序号列rn，进行行转列，但是行转列并不能让我们将所有的分数行一起转（把分数作为整体），因为行转列只能操作列，而不是表。通过Oracle的字符串连接，把我们需要行转列的数据进行拼串，这样的话，可以将拼串的列进行行转列（因为他是一个列而不是表）
-
第二步
此时使用的行转列是“ pivot（聚合函数 for 列名 in（类型）），其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 select distinct ranking from temp”，参考：“www.cnblogs.com/xiao02fang/…”
SELECT * FROM [StudentScores] /*数据源*/
AS P
PIVOT
(
SUM(Score/*行转列后 列的值*/) FOR
p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)
) AS T
- 第三步
经过上步转换后的结果为：此时就可以将每个项目的五个专家的评分做成我们需要的格式，而其中有空的（原因是该项目只有四个专家进行评分），下一步就是通过key_no,将我们需要的其他字段进行连接查询，得到的结果集就是每个项目的所需要导出的信息。对于后面拼接的字符串可以通过Excel的功能进行切割（Excel的分列功能）。
- 第四步
拼接其他需要的数据列后，得到需要的结果集： - 第五步
使用trrim(regexp_substr())对拼接的字符串做字符串切割，分为多列
- sql代码
--用于数据导出
select * from (
(
select distinct v.*
from (select ec.key_no,
p.zh_title,
ca.name as area_name,
o.name as org_name,
psn.zh_name,
eg.group_cname,
(select count(0)
from evaluation t
where t.status in ('05', '06', '07', '09')
and t.atc_code = ec.atc_code) as receivable,
(select count(e.ev_code)
from evaluation e
where e.atc_code = ec.atc_code
and e.status in ('09')) as received,
(select count(e.ev_code)
from evaluation e
where e.atc_code = ec.atc_code
and e.status in ('06')) as advoided,
ec.total_score
from evatv_container ec
left join evaluation e on e.atc_code = ec.atc_code
left join evatv_group eg on eg.group_code = ec.group_code
left join proposal p on p.prp_code = ec.key_code
left join proposal_extend pe on pe.prp_code=p.prp_code
left join const_area ca on ca.area_no=pe.area_value
left join organization o on o.org_code = p.org_code
left join person psn on psn.psn_code = ec.psn_code
where 1 = 1
and eg.atv_code = 2455) v )d
left join
(select * from
(select c.key_no,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 1), ',') AS c11,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 2), ',') AS c21,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 3), ',') AS c31,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 4), ',') AS c41,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 5), ',') AS c51,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 6), ',') AS c61,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 7), ',') AS c71,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 8), ',') AS c81,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 9), ',') AS c91,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 10), ',') AS c101,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 1), ',') AS c12,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 2), ',') AS c22,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 3), ',') AS c32,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 4), ',') AS c42,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 5), ',') AS c52,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 6), ',') AS c62,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 7), ',') AS c72,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 8), ',') AS c82,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 9), ',') AS c92,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 10), ',') AS c102,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 1), ',') AS c13,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 2), ',') AS c23,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 3), ',') AS c33,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 4), ',') AS c43,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 5), ',') AS c53,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 6), ',') AS c63,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 7), ',') AS c73,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 8), ',') AS c83,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 9), ',') AS c93,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 10), ',') AS c103,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 1), ',') AS c14,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 2), ',') AS c24,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 3), ',') AS c34,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 4), ',') AS c44,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 5), ',') AS c54,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 6), ',') AS c64,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 7), ',') AS c74,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 8), ',') AS c84,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 9), ',') AS c94,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 10), ',') AS c104,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 1), ',') AS c15,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 2), ',') AS c25,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 3), ',') AS c35,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 4), ',') AS c45,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 5), ',') AS c55,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 6), ',') AS c65,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 7), ',') AS c75,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 8), ',') AS c85,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 9), ',') AS c95,
rtrim(regexp_substr(p1 || ',', '.*?' || ',', 1, 10), ',') AS c105
FROM
(select * from
(
select b.* from evatv_container ec ,
(select row_number() over(PARTITION BY a.key_no ORDER BY a.ev_code) AS rn,a.key_no,
a.total_score || ',' || a.zb1_1 || ',' || a.zb1_2 || ',' || a.zb1_3 || ',' || a.zb2_1 || ',' || a.zb2_2 || ',' || a.zb2_3 || ',' || a.zb3_1 || ',' || a.zb3_2 || ',' || a.zb3_3 as p from
(select ec.key_no,e.ev_code, e.total_score,
sum(decode(ei.seq_no, '2', option_score,null)) as zb1_1,
sum(decode(ei.seq_no, '3', option_score,null)) as zb1_2,
sum(decode(ei.seq_no, '4', option_score,null)) as zb1_3,
sum(decode(ei.seq_no, '6', option_score,null)) as zb2_1,
sum(decode(ei.seq_no, '7', option_score,null)) as zb2_2,
sum(decode(ei.seq_no, '8', option_score,null)) as zb2_3,
sum(decode(ei.seq_no, '10', option_score,null)) as zb3_1,
sum(decode(ei.seq_no, '11', option_score,null)) as zb3_2,
sum(decode(ei.seq_no, '12', option_score,null)) as zb3_3
from evaluation e
left join evatv_container ec on e.atc_code = ec.atc_code
left join evatv_group eg on eg.group_code = ec.group_code
left join evform_items ei on ei.form_code = e.form_code
left join evaluation_result er on er.ev_code = e.ev_code and er.item_code = ei.item_code
left join proposal p on p.prp_code = ec.key_code
where eg.atv_code = 2455 and p.grant_code = 48327 and e.status='09' and er.option_score is not null
group by ec.key_no,e.ev_code,e.total_score
order by ec.key_no,e.ev_code asc)a
)b where ec.key_no=b.key_no
)
pivot (max(p) for rn in( '1' as p1, '2' as p2,'3' as p3, '4' as p4, '5' as p5)) )c))e
on d.key_no=e.key_no
)
文末：希望各路大神不吝赐教！
