关于使用Oracle行转列的随笔
参考博客：Oracle 行列转换详解yuncode.net/code/c_59bf…
-
需要将数据库中的查询结果，导出为指定格式（数据列排版）Excel文件
-
过程及说明可看# oracle中复杂的行转列（第一弹）juejin.cn/post/705742…
-
sql（这是一段很蠢的sql）：
select * from(
(select distinct v.*
from
(select eva.atv_code, eva.atv_cname,eva.create_time,eva.start_date,eva.end_date
from evactivity eva
left join evatv_group eg
on eva.atv_code = eg.atv_code
left join expert_extract_result eer
on eer.group_code = eg.group_code
left join evreviewer e
on eer.psn_code = e.psn_code
left join person p
on e.psn_code = p.psn_code
left join const_dictionary cd
on cd.category = 'expert_type'
and cd.code = eer.expert_type
left join org_office oo
on eva.kfyw_org_code=oo.off_org_code
where eva.atv_type = 5
and eer.assign_flag = '3'
and oo.parent_org_code = 9000005897
and eva.STAT_YEAR = '2020'
order by eva.ATV_CODE asc)v)a
left join
(SELECT t.atv_code,t.expert_type ,
MAX(decode(rn, 1, zh_name, NULL)) ||
MAX(decode(rn, 2, ',' || zh_name, NULL)) ||
MAX(decode(rn, 3, ',' || zh_name, NULL)) ||
MAX(decode(rn, 4, ',' || zh_name, NULL)) ||
MAX(decode(rn, 5, ',' || zh_name, NULL)) ||
MAX(decode(rn, 6, ',' || zh_name, NULL)) ||
MAX(decode(rn, 7, ',' || zh_name, NULL)) ||
MAX(decode(rn, 8, ',' || zh_name, NULL)) ||
MAX(decode(rn, 9, ',' || zh_name, NULL)) ||
MAX(decode(rn, 10, ',' || zh_name, NULL)) ||
MAX(decode(rn, 11, ',' || zh_name, NULL)) ||
MAX(decode(rn, 12, ',' || zh_name, NULL)) ||
MAX(decode(rn, 13, ',' || zh_name, NULL)) ||
MAX(decode(rn, 14, ',' || zh_name, NULL)) ||
MAX(decode(rn, 15, ',' || zh_name, NULL)) ||
MAX(decode(rn, 16, ',' || zh_name, NULL)) ||
MAX(decode(rn, 17, ',' || zh_name, NULL)) ||
MAX(decode(rn, 18, ',' || zh_name, NULL)) ||
MAX(decode(rn, 19, ',' || zh_name, NULL)) ||
MAX(decode(rn, 20, ',' || zh_name, NULL)) ||
MAX(decode(rn, 21, ',' || zh_name, NULL)) ||
MAX(decode(rn, 22, ',' || zh_name, NULL)) ||
MAX(decode(rn, 23, ',' || zh_name, NULL)) ||
MAX(decode(rn, 24, ',' || zh_name, NULL)) ||
MAX(decode(rn, 25, ',' || zh_name, NULL)) ||
MAX(decode(rn, 26, ',' || zh_name, NULL)) ||
MAX(decode(rn, 27, ',' || zh_name, NULL)) ||
MAX(decode(rn, 28, ',' || zh_name, NULL)) ||
MAX(decode(rn, 29, ',' || zh_name, NULL)) ||
MAX(decode(rn, 30, ',' || zh_name, NULL)) ||
MAX(decode(rn, 31, ',' || zh_name, NULL)) ||
MAX(decode(rn, 32, ',' || zh_name, NULL)) ||
MAX(decode(rn, 33, ',' || zh_name, NULL)) ||
MAX(decode(rn, 34, ',' || zh_name, NULL)) ||
MAX(decode(rn, 35, ',' || zh_name, NULL)) ||
MAX(decode(rn, 36, ',' || zh_name, NULL)) ||
MAX(decode(rn, 37, ',' || zh_name, NULL)) ||
MAX(decode(rn, 38, ',' || zh_name, NULL)) ||
MAX(decode(rn, 39, ',' || zh_name, NULL)) ||
MAX(decode(rn, 40, ',' || zh_name, NULL)) ||
MAX(decode(rn, 41, ',' || zh_name, NULL)) ||
MAX(decode(rn, 42, ',' || zh_name, NULL)) ||
MAX(decode(rn, 43, ',' || zh_name, NULL)) ||
MAX(decode(rn, 44, ',' || zh_name, NULL)) ||
MAX(decode(rn, 45, ',' || zh_name, NULL)) ||
MAX(decode(rn, 46, ',' || zh_name, NULL)) ||
MAX(decode(rn, 47, ',' || zh_name, NULL)) ||
MAX(decode(rn, 48, ',' || zh_name, NULL)) ||
MAX(decode(rn, 49, ',' || zh_name, NULL)) ||
MAX(decode(rn, 50, ',' || zh_name, NULL)) ||
MAX(decode(rn, 51, ',' || zh_name, NULL)) ||
MAX(decode(rn, 52, ',' || zh_name, NULL)) ||
MAX(decode(rn, 53, ',' || zh_name, NULL)) ||
MAX(decode(rn, 54, ',' || zh_name, NULL)) ||
MAX(decode(rn, 55, ',' || zh_name, NULL)) ||
MAX(decode(rn, 56, ',' || zh_name, NULL)) ||
MAX(decode(rn, 57, ',' || zh_name, NULL)) ||
MAX(decode(rn, 58, ',' || zh_name, NULL)) ||
MAX(decode(rn, 59, ',' || zh_name, NULL)) ||
MAX(decode(rn, 60, ',' || zh_name, NULL)) ||
MAX(decode(rn, 61, ',' || zh_name, NULL)) ||
MAX(decode(rn, 62, ',' || zh_name, NULL)) ||
MAX(decode(rn, 63, ',' || zh_name, NULL)) ||
MAX(decode(rn, 64, ',' || zh_name, NULL)) ||
MAX(decode(rn, 65, ',' || zh_name, NULL)) ||
MAX(decode(rn, 66, ',' || zh_name, NULL)) ||
MAX(decode(rn, 67, ',' || zh_name, NULL)) ||
MAX(decode(rn, 68, ',' || zh_name, NULL)) ||
MAX(decode(rn, 69, ',' || zh_name, NULL)) ||
MAX(decode(rn, 70, ',' || zh_name, NULL)) ||
MAX(decode(rn, 71, ',' || zh_name, NULL)) ||
MAX(decode(rn, 72, ',' || zh_name, NULL)) ||
MAX(decode(rn, 73, ',' || zh_name, NULL)) ||
MAX(decode(rn, 74, ',' || zh_name, NULL)) ||
MAX(decode(rn, 75, ',' || zh_name, NULL)) ||
MAX(decode(rn, 76, ',' || zh_name, NULL)) ||
MAX(decode(rn, 77, ',' || zh_name, NULL)) ||
MAX(decode(rn, 78, ',' || zh_name, NULL)) ||
MAX(decode(rn, 79, ',' || zh_name, NULL)) ||
MAX(decode(rn, 80, ',' || zh_name, NULL)) ||
MAX(decode(rn, 81, ',' || zh_name, NULL)) ||
MAX(decode(rn, 82, ',' || zh_name, NULL)) ||
MAX(decode(rn, 83, ',' || zh_name, NULL)) ||
MAX(decode(rn, 84, ',' || zh_name, NULL)) ||
MAX(decode(rn, 85, ',' || zh_name, NULL)) ||
MAX(decode(rn, 86, ',' || zh_name, NULL)) ||
MAX(decode(rn, 87, ',' || zh_name, NULL)) ||
MAX(decode(rn, 88, ',' || zh_name, NULL)) ||
MAX(decode(rn, 89, ',' || zh_name, NULL)) ||
MAX(decode(rn, 90, ',' || zh_name, NULL)) ||
MAX(decode(rn, 91, ',' || zh_name, NULL)) ||
MAX(decode(rn, 92, ',' || zh_name, NULL)) ||
MAX(decode(rn, 93, ',' || zh_name, NULL)) ||
MAX(decode(rn, 94, ',' || zh_name, NULL)) ||
MAX(decode(rn, 95, ',' || zh_name, NULL)) ||
MAX(decode(rn, 96, ',' || zh_name, NULL)) ||
MAX(decode(rn, 97, ',' || zh_name, NULL)) ||
MAX(decode(rn, 98, ',' || zh_name, NULL)) ||
MAX(decode(rn, 99, ',' || zh_name, NULL)) ||
MAX(decode(rn, 100, ',' || zh_name, NULL)) ||
MAX(decode(rn, 101, ',' || zh_name, NULL)) ||
MAX(decode(rn, 102, ',' || zh_name, NULL)) ||
MAX(decode(rn, 103, ',' || zh_name, NULL)) ||
MAX(decode(rn, 104, ',' || zh_name, NULL)) ||
MAX(decode(rn, 105, ',' || zh_name, NULL)) ||
MAX(decode(rn, 106, ',' || zh_name, NULL)) ||
MAX(decode(rn, 107, ',' || zh_name, NULL)) ||
MAX(decode(rn, 108, ',' || zh_name, NULL)) ||
MAX(decode(rn, 109, ',' || zh_name, NULL)) ||
MAX(decode(rn, 110, ',' || zh_name, NULL)) ||
MAX(decode(rn, 111, ',' || zh_name, NULL)) ||
MAX(decode(rn, 112, ',' || zh_name, NULL)) ||
MAX(decode(rn, 113, ',' || zh_name, NULL)) ||
MAX(decode(rn, 114, ',' || zh_name, NULL)) ||
MAX(decode(rn, 115, ',' || zh_name, NULL)) ||
MAX(decode(rn, 116, ',' || zh_name, NULL)) ||
MAX(decode(rn, 117, ',' || zh_name, NULL)) ||
MAX(decode(rn, 118, ',' || zh_name, NULL)) ||
MAX(decode(rn, 119, ',' || zh_name, NULL)) ||
MAX(decode(rn, 120, ',' || zh_name, NULL)) ||
MAX(decode(rn, 121, ',' || zh_name, NULL)) ||
MAX(decode(rn, 122, ',' || zh_name, NULL)) ||
MAX(decode(rn, 123, ',' || zh_name, NULL)) ||
MAX(decode(rn, 124, ',' || zh_name, NULL)) ||
MAX(decode(rn, 125, ',' || zh_name, NULL)) ||
MAX(decode(rn, 126, ',' || zh_name, NULL)) ||
MAX(decode(rn, 127, ',' || zh_name, NULL)) ||
MAX(decode(rn, 128, ',' || zh_name, NULL)) ||
MAX(decode(rn, 129, ',' || zh_name, NULL)) ||
MAX(decode(rn, 130, ',' || zh_name, NULL)) ||
MAX(decode(rn, 131, ',' || zh_name, NULL)) ||
MAX(decode(rn, 132, ',' || zh_name, NULL)) ||
MAX(decode(rn, 133, ',' || zh_name, NULL)) ||
MAX(decode(rn, 134, ',' || zh_name, NULL)) ||
MAX(decode(rn, 135, ',' || zh_name, NULL)) ||
MAX(decode(rn, 136, ',' || zh_name, NULL)) ||
MAX(decode(rn, 137, ',' || zh_name, NULL)) ||
MAX(decode(rn, 138, ',' || zh_name, NULL)) ||
MAX(decode(rn, 139, ',' || zh_name, NULL)) ||
MAX(decode(rn, 140, ',' || zh_name, NULL)) str
FROM
(select eva.atv_code,cd.zh_cn_caption as expert_type,
(p.zh_name || ' ' || decode(nvl(ece.total_score,-1),-1,'无',ece.total_score)) as zh_name,
row_number() over(PARTITION BY eva.atv_code,cd.zh_cn_caption ORDER BY eva.ATV_CODE desc,cd.zh_cn_caption) AS rn
from evactivity eva
left join evatv_group eg
on eva.atv_code = eg.atv_code
left join expert_extract_result eer
on eer.group_code = eg.group_code
left join evreviewer e
on eer.psn_code = e.psn_code
left join person p
on e.psn_code = p.psn_code
left join const_dictionary cd
on cd.category = 'expert_type'
and cd.code = eer.expert_type
left join org_office oo
on eva.kfyw_org_code=oo.off_org_code
left join evatv_credit_evaluation ece
on ece.atv_code = eva.atv_code
and ece.psn_code = p.psn_code
where eva.atv_type = 5
and eer.assign_flag = '3'
and oo.parent_org_code = 9000005897
and eva.STAT_YEAR = '2020'
group by eva.atv_code,cd.zh_cn_caption ,p.zh_name,ece.total_score
order by eva.ATV_CODE asc,expert_type)t
GROUP BY t.atv_code,t.expert_type )c
on a.atv_code=c.atv_code
left join
(select atv_code, expert_type,count(0) from
(select eva.atv_code,
cd.zh_cn_caption as expert_type,
p.zh_name,
row_number() over(PARTITION BY eva.atv_code, cd.zh_cn_caption ORDER BY eva.ATV_CODE desc, cd.zh_cn_caption) AS rn
from evactivity eva
left join evatv_group eg
on eva.atv_code = eg.atv_code
left join expert_extract_result eer
on eer.group_code = eg.group_code
left join evreviewer e
on eer.psn_code = e.psn_code
left join person p
on e.psn_code = p.psn_code
left join const_dictionary cd
on cd.category = 'expert_type'
and cd.code = eer.expert_type
left join org_office oo
on eva.kfyw_org_code = oo.off_org_code
left join evatv_credit_evaluation ece
on ece.atv_code = eva.atv_code
and ece.psn_code = p.psn_code
where eva.atv_type = 5
and eer.assign_flag = '3'
and oo.parent_org_code = 9000005897
and eva.STAT_YEAR = '2020'
and ece.total_score is null
group by eva.atv_code, cd.zh_cn_caption, p.zh_name, ece.total_score
order by eva.ATV_CODE desc, expert_type)
group by atv_code, expert_type
order by atv_code asc, expert_type)e
on a.atv_code=e.atv_code
and c.expert_type=e.expert_type
)
order by a.atv_code asc
文末：希望各路大神不吝赐教！
