最近收到一个小需求,长期不写SQL,颇为费了一番周折。
如图:
源数据类似于这样
列名1 | 列名2 |
a | c |
a | d |
b | c |
转换后类似于这样
列名1 | 列名2 | 列名2(2) |
a | c | d |
b | c |
因源数据有保密性要求,此处只提供SQL语句供大家参考:
select fybm,
max(case when rn=1 then htbm else null end) htbm1,
max(case when rn=1 then xyksrq else null end) xyksrq1,
max(case when rn=1 then xyjsrq else null end) xyjsrq1,
max(case when rn=1 then htztmc else null end) htztmc1,
max(case when rn=2 then htbm else null end) htbm2,
max(case when rn=2 then xyksrq else null end) xyksrq2,
max(case when rn=2 then xyjsrq else null end) xyjsrq2,
max(case when rn=2 then htztmc else null end) htztmc2,
max(case when rn=3 then htbm else null end) htbm3,
max(case when rn=3 then xyksrq else null end) xyksrq3,
max(case when rn=3 then xyjsrq else null end) xyjsrq3,
max(case when rn=3 then htztmc else null end) htztmc3,
max(case when rn=4 then htbm else null end) htbm4,
max(case when rn=4 then xyksrq else null end) xyksrq4,
max(case when rn=4 then xyjsrq else null end) xyjsrq4,
max(case when rn=4 then htztmc else null end) htztmc4,
max(case when rn=5 then htbm else null end) htbm5,
max(case when rn=5 then xyksrq else null end) xyksrq5,
max(case when rn=5 then xyjsrq else null end) xyjsrq5,
max(case when rn=5 then htztmc else null end) htztmc5,
max(case when rn=6 then htbm else null end) htbm6,
max(case when rn=6 then xyksrq else null end) xyksrq6,
max(case when rn=6 then xyjsrq else null end) xyjsrq6,
max(case when rn=6 then htztmc else null end) htztmc6
--max(case when rn=7 then htbm else null end) htbm7
from (
select fybm,
htbm,
xyksrq,
xyjsrq,
htztmc,
row_number() over(partition by fybm order by htbm desc ) rn from temp_20230209_lj a)
group by fybm