impala行转列问题

由于impala不能使用 LATERAL VIEW EXPLODE
需要行转列时，可采用加辅助列的方式：

DROP TABLE IF EXISTS ZHYW.T0;
CREATE TABLE IF NOT EXISTS ZHYW.T0 AS 
SELECT '1' AS NM,'A,B,C' AS ID 
UNION ALL
SELECT '2' AS NM,'A,B,C,D' AS ID 
;

DROP TABLE IF EXISTS ZHYW.T01;
CREATE TABLE IF NOT EXISTS ZHYW.T01 AS 
SELECT NM
,LENGTH(REGEXP_REPLACE(ID,'[A-Z]',''))+1 AS MAX_LOC
FROM ZHYW.T0 ;

DROP TABLE IF EXISTS ZHYW.T1;
CREATE TABLE IF NOT EXISTS ZHYW.T1 AS 
SELECT '1' AS NUM
UNION ALL
SELECT '2' AS NUM
UNION ALL
SELECT '3' AS NUM
UNION ALL
SELECT '4' AS NUM
UNION ALL
SELECT '5' AS NUM
UNION ALL
SELECT '6' AS NUM
UNION ALL
SELECT '7' AS NUM
;

行转列结果展示：

WITH  T2 AS ( SELECT NM,MAX_LOC FROM ZHYW.T01 )                                   -- 注意 WITH后面的表名 如果表名前加了库名会报错
     ,T3 AS (SELECT ROW_NUMBER() OVER (ORDER BY NUM ) AS RN FROM ZHYW.T1)         -- 这里为了得到一列从1开始的自然数，也可以选任意一张表的主键，根据主键排序可以取从1开始的自然数

SELECT T0.NM,T0.ID,T4.RN,SPLIT_PART(T0.ID,',',T4.RN) AS SPLIT_ID
FROM ZHYW.T0   T0
LEFT JOIN (
        SELECT T2.NM,T2.MAX_LOC,T3.RN
        FROM  T2
        LEFT JOIN  T3
        ON CAST(T2.MAX_LOC AS INT) >= T3.RN
) T4
ON T0.NM = T4.NM
;