WITH BASEQ
AS
(
select * from UT_TEST
UNPIVOT
(
SCRVAL for SCRNAME in (
scr1 as 'scr1',
scr2 as 'scr2',
scr3 as 'scr3'
)
)
)
SELECT BASEQ.CUSTID, LISTAGG(BASEQ.SCRNAME,',') WITHIN GROUP (ORDER BY BASEQ.SCRVAL DESC) FINALCOL FROM BASEQ
GROUP BY BASEQ.CUSTID;
select custid
,concat_ws
(
','
,max (case when rn = 1 then col end)
,max (case when rn = 2 then col end)
,max (case when rn = 3 then col end)
) as str
from (select custid
,elt (pe.pos+1,'scr1','scr2','scr3') as col
,row_number () over
(
partition by t.custid
order by pe.val desc
) rn
from mytable t lateral view posexplode (array (scr1,scr2,scr3)) pe
) t
group by t.custid
select custid
,concat_ws (',',scr[0].col2,scr[1].col2,scr[2].col2) as str
from (select custid
,sort_array
(
array
(
struct(-scr1,'scr1')
,struct(-scr2,'scr2')
,struct(-scr3,'scr3')
)
) as scr
from mytable
) t
另一种方法是使用 union all 以及基于scr1、scr2、scr3值分配行号。然后按custid聚合以生成csv值。
with rownums as
(select t.*,row_number() over(partition by custid order by cast(scr as int) desc) as rnum
from (select custid,scr1 as scr,'scr1' as col from mytable
union all
select custid,scr2 as scr,'scr2' as col from mytable
union all
select custid,scr3 as scr,'scr3' as col from mytable
) t
)
select custid,concat(max(case when rnum=1 then col end),',',max(case when rnum=2 then col end),',',max(case when rnum=3 then col end))
from rownums
group by custid
4条答案
按热度按时间ctehm74n1#
试试这个。。
第一步。首先将初始查询集解压到baseq中
基准Q:
结果集:
第二步:然后使用listag
这是密码
最终结果集:
wsewodh22#
mspsb9vt3#
这是我的首选方案
brqmpdu14#
另一种方法是使用
union all
以及基于scr1、scr2、scr3值分配行号。然后按custid聚合以生成csv值。