原表数据如下,业务场景是取用户的top5站点访问情况，行号是根据访问次数降序生成。
用户ID | 站点名 | 流量 |访问次数 |行号
user_id |site_name |all_flow |net_times |rn
1 a 10 5 1
1 b 23 4 2
1 c 56 3 3
1 d 12 2 4
1 e 14 1 5
下一步需求是将多列转为单行，如下
user_id|top1_name|top1_times|top1_flow|top2_name|top2_times|top2_flow|top3_name|top3_times|top3_flow|top4_name|top4_times|top4_flow|top5_name|top5_times|top5_flow
1|a|5|10|b|4|23|c|3|56|d|2|12|e|1|14
转换代码：
SELECT USER_ID,
MAX(CASE WHEN RN = 1 THEN SITE_NAME END) AS TOP1_NAME,
SUM(CASE WHEN RN = 1 THEN NET_TIMES ELSE CAST( '0' AS BIGINT) END) AS TOP1_TIMES,
SUM(CASE WHEN RN = 1 THEN ALLFLOW ELSE CAST( '0' AS BIGINT) END) AS TOP1_FLOW,
MAX(CASE WHEN RN = 2 THEN SITE_NAME END) AS TOP2_NAME,
SUM(CASE WHEN RN = 2 THEN NET_TIMES ELSE CAST( '0' AS BIGINT) END) AS TOP2_TIMES,
SUM(CASE WHEN RN = 2 THEN ALLFLOW ELSE CAST( '0' AS BIGINT) END) AS top2_flow,
MAX(CASE WHEN RN = 3 THEN SITE_NAME END) AS TOP3_NAME,
SUM(CASE WHEN RN = 3 THEN NET_TIMES ELSE CAST( '0' AS BIGINT) END) AS top3_times,
SUM(CASE WHEN RN = 3 THEN ALLFLOW ELSE CAST( '0' AS BIGINT) END) AS top3_flow,
MAX(CASE WHEN RN = 4 THEN SITE_NAME END) AS TOP4_NAME,
SUM(CASE WHEN RN = 4 THEN NET_TIMES ELSE CAST( '0' AS BIGINT) END) AS top4_times,
SUM(CASE WHEN RN = 4 THEN ALLFLOW ELSE CAST( '0' AS BIGINT) END) AS top4_flow,
MAX(CASE WHEN RN = 5 THEN SITE_NAME END) AS TOP5_NAME,
SUM(CASE WHEN RN = 5 THEN NET_TIMES ELSE CAST( '0' AS BIGINT) END) AS top5_times,
SUM(CASE WHEN RN = 5 THEN ALLFLOW ELSE CAST( '0' AS BIGINT) END) AS top5_flow
FROM ... where ...
GROUP BY USER_ID ORDER BY USER_ID DESC;