Teradata中SQL的Transpose/Unpivot解决方案需求（禁用CASE/UNION）

问题说明 #

现有sample_table表，字段包含city、grp1-grp4、token1-token4，其中grpN与tokenN一一对应（N=1~4）。需将表转置得到8行结果，禁止使用CASE、UNION/UNION ALL语句。

原始数据示例 #

city     |    grp1|    grp2|    grp3|    grp4|token1|token2|token3|token4
('mumbai','111111','222222','333333','444444' ,1001,  2001,  3001, 4001),
('pune','555555','666666','777777','888888',   1002,  2002,  3002,  4002);

期望输出（8行） #

city,grp_consolidated,token_cons
mumbai,111111,1001
mumbai,222222,2001
mumbai,333333,3001
mumbai,444444,4001
pune,555555,1002
pune,666666,2002
pune,777777,3002
pune,888888,4002

表结构与插入语句 #

CREATE TABLE sample_table(
  city VARCHAR(8),
  grp1 VARCHAR(8),
  grp2 VARCHAR(8),
  grp3 VARCHAR(8),
  grp4 VARCHAR(8),
  token1 DECIMAL(31,18),
  token2 DECIMAL(31,18),
  token3 DECIMAL(31,18),
  token4 DECIMAL(31,18)
);
INSERT INTO sample_table
VALUES
('mumbai','111111','222222','333333','444444',1001,2001,3001,4001),
('pune','555555','666666','777777','888888',1002,2002,3002,4002);

解决方案 #

1. PostgreSQL 实现 #

利用unnest函数同时展开对应数组，自动按位置配对grp和token：

SELECT 
    city,
    unnest(array[grp1, grp2, grp3, grp4]) AS grp_consolidated,
    unnest(array[token1, token2, token3, token4]) AS token_cons
FROM sample_table;

2. SQL Server 实现 #

通过两次UNPIVOT拆分字段，再过滤后缀匹配的行确保对应关系：

SELECT 
    city,
    grp_consolidated,
    token_cons
FROM sample_table
UNPIVOT (
    grp_consolidated FOR grp_col IN (grp1, grp2, grp3, grp4)
) AS unpvt_grp
UNPIVOT (
    token_cons FOR token_col IN (token1, token2, token3, token4)
) AS unpvt_token
WHERE RIGHT(grp_col, 1) = RIGHT(token_col, 1);

3. MySQL 8.0+ 实现 #

使用JSON_TABLE将字段转为JSON数组后展开，保证对应关系：

SELECT 
    t.city,
    j.grp_consolidated,
    j.token_cons
FROM sample_table t
JOIN JSON_TABLE(
    CONCAT('[',
        JSON_ARRAY(t.grp1, t.token1), ',',
        JSON_ARRAY(t.grp2, t.token2), ',',
        JSON_ARRAY(t.grp3, t.token3), ',',
        JSON_ARRAY(t.grp4, t.token4),
    ']'),
    '$[*]' COLUMNS (
        grp_consolidated VARCHAR(8) PATH '$[0]',
        token_cons DECIMAL(31,18) PATH '$[1]'
    )
) j;

内容的提问来源于stack exchange，提问作者AKASH GANJAL