试试这个。。
第一步。首先将初始查询集解压到baseq中
基准Q：

select * from UT_TEST
            UNPIVOT 
            (
                SCRVAL for SCRNAME in (
                scr1 as 'scr1',
                scr2 as 'scr2',
                scr3 as 'scr3'
            )
        )

结果集：

1111    scr1    1
        1111    scr2    2
        1111    scr3    3
        2222    scr1    4
        2222    scr2    3
        2222    scr3    2
        3333    scr1    4
        3333    scr2    5
        3333    scr3    3

第二步：然后使用listag
这是密码

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;

最终结果集：

1111    scr3,scr2,scr1
        2222    scr1,scr2,scr3
        3333    scr2,scr1,scr3

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

+--------+----------------+
| custid |      str       |
+--------+----------------+
|   1111 | scr3,scr2,scr1 |
|   2222 | scr1,scr2,scr3 |
|   3333 | scr2,scr1,scr3 |
+--------+----------------+

这是我的首选方案

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

+--------+----------------+
| custid |      str       |
+--------+----------------+
|   1111 | scr3,scr2,scr1 |
|   2222 | scr1,scr2,scr3 |
|   3333 | scr2,scr1,scr3 |
+--------+----------------+

另一种方法是使用 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