hive行列互换不使用TRANSPOSE

Hive中没有直接的行列互换函数，但可以通过以下方式实现：

1. 使用CASE WHEN语句将列转换为行

例如，假设有以下表格：

id   | name | age | gender
-----|------|-----|-------
1    | Alice| 25  | F
2    | Bob  | 30  | M
3    | Chris| 27  | M

我们想要将它转换为以下形式：

field  | 1    | 2    | 3    
-------|------|------|------
name   | Alice| Bob  | Chris
age    | 25   | 30   | 27   
gender | F    | M    | M    

可以使用以下查询语句：

SELECT 'name' AS field, 
       MAX(CASE WHEN id = 1 THEN name END) AS 1,
       MAX(CASE WHEN id = 2 THEN name END) AS 2,
       MAX(CASE WHEN id = 3 THEN name END) AS 3
FROM my_table
UNION ALL
SELECT 'age' AS field, 
       MAX(CASE WHEN id = 1 THEN age END) AS 1,
       MAX(CASE WHEN id = 2 THEN age END) AS 2,
       MAX(CASE WHEN id = 3 THEN age END) AS 3
FROM my_table
UNION ALL
SELECT 'gender' AS field, 
       MAX(CASE WHEN id = 1 THEN gender END) AS 1,
       MAX(CASE WHEN id = 2 THEN gender END) AS 2,
       MAX(CASE WHEN id = 3 THEN gender END) AS 3
FROM my_table;

2. 使用LATERAL VIEW EXPLODE函数将行转换为列

假设有以下表格：

id   | category | value
-----|----------|------
1    | A        | 10
1    | B        | 20
1    | C        | 30
2    | A        | 40
2    | B        | 50
2    | C        | 60

我们想要将它转换为以下形式：

id   | A | B | C
-----|---|---|---
1    | 10| 20| 30
2    | 40| 50| 60

可以使用以下查询语句：

SELECT id, 
       MAX(CASE WHEN category = 'A' THEN value END) AS A,
       MAX(CASE WHEN category = 'B' THEN value END) AS B,
       MAX(CASE WHEN category = 'C' THEN value END) AS C
FROM my_table
LATERAL VIEW EXPLODE(array('A', 'B', 'C')) exploded_table AS category
GROUP BY id;