SQL中经常写行转列、列转行的语句，除了case when、聚合函数，还有没有更高效、更简洁的写法？

Oracle 11g之后提供了自带的函数unpivot实现行转列，并可支持多列同时转换，写一段SQL供参考运行： SELECT CONTID, LAST_UPDATE_USER, REF_NUM, CONT_METH_TP_CD, ROW_NUMBER() OVER(PARTITION BY CONTID, REF_NUM ORDER BY DATAPKID) NUM FROM (SELECT T.CONTID, T.IMPORTUSERCODE AS LAST_UPDATE_USER, CASE WHEN TRIM(T.MOBILEPHONE_NUM1) IS NOT NULL THEN 8 END AS MCD1, CASE WHEN TRIM(T.MOBILEPHONE_NUM2) IS NOT NULL THEN 8 END AS MCD2, CASE WHEN TRIM(T.MOBILEPHONE_NUM3) IS NOT NULL THEN 8 END AS MCD3, CASE WHEN TRIM(T.PHONE_NUM1) IS NOT NULL THEN 9 END AS PCD1, CASE WHEN TRIM(T.PHONE_NUM2) IS NOT NULL THEN 9 END AS PCD2, T.MOBILEPHONE_NUM1 AS M1, T.MOBILEPHONE_NUM2 AS M2, T.MOBILEPHONE_NUM3 AS M3, T.PHONE_NUM1 AS P1, T.PHONE_NUM2 AS P2, T.DATAPKID FROM RESOURCE_DATA T WHERE T.CONTID IS NOT NULL) UNPIVOT((REF_NUM, CONT_METH_TP_CD) FOR TYPE IN((M1, MCD1),(M2, MCD2),(M3, MCD3),(P1, PCD1),(P2, PCD2)));

示例数据 假设已有一张表pageAds，它有三列数据，第一列是pageid string，第二列是col1 array，第三列是col2 array，详细数据如下。 pageid col1 col2 front_page [1, 2, 3] [“a”, “b”, “c”] contact_page [3, 4, 5] [“d”, “e”, “f”]

使用示例 单个Lateral View语句 示例1：拆分col1。命令示例如下： select pageid, col1_new, col2 from pageAds lateral view explode(col1) adTable as col1_new;

返回结果如下： ±-----------±-----------±-----------+ | pageid | col1_new | col2 | ±-----------±-----------±-----------+ | front_page | 1 | [“a”,“b”,“c”] | | front_page | 2 | [“a”,“b”,“c”] | | front_page | 3 | [“a”,“b”,“c”] | | contact_page | 3 | [“d”,“e”,“f”] | | contact_page | 4 | [“d”,“e”,“f”] | | contact_page | 5 | [“d”,“e”,“f”] | ±-----------±-----------±-----------+ ———————————————— 版权声明：本文为CSDN博主「vthinkwen」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/qq_24836169/article/details/113886217

count，avg,sum,max,min 等聚合函数