如何编写SQL查询实现行转列并生成所有组合？

你用collect_set只能把同一id下的name聚合为一个集合，没法生成集合内元素的各类组合，要实现这个需求，得用**自连接（Self Join）**来生成所有可能的配对，再过滤掉重复的无效组合。

先拿示例数据说清楚场景 #

假设你的Table1是这样的：

-- Table1
id | name
1  | a
1  | b
1  | c
2  | x
2  | y

要生成的目标Table2是同id下name的两两不重复组合：

-- Table2（目标）
id | name1 | name2
1  | a     | b
1  | a     | c
1  | b     | c
2  | x     | y

直接能用的SQL代码 #

SELECT 
    t1.id,
    t1.name AS name1,
    t2.name AS name2
FROM 
    Table1 t1
JOIN 
    Table1 t2 ON t1.id = t2.id 
             AND t1.name < t2.name; -- 去掉这条就会生成包括a-b、b-a的双向组合

为啥这么写？ #

• 自连接：把表自己和自己连起来，要求id相同，这样就能拿到同一id下所有name的配对。
• t1.name < t2.name：用字符串大小比较避免重复组合，要是你需要双向的所有组合，把这个条件换成t1.name != t2.name就行。

要是需要生成多元素子集组合怎么办？ #

比如要单个元素、两两、三个元素的所有非重复子集，用递归CTE就能实现（以Hive SQL为例）：

WITH RECURSIVE combinations AS (
    SELECT 
        id,
        name AS combo,
        name AS last_name,
        1 AS level
    FROM Table1
    UNION ALL
    SELECT 
        c.id,
        CONCAT(c.combo, ',', t.name) AS combo,
        t.name AS last_name,
        c.level + 1 AS level
    FROM combinations c
    JOIN Table1 t ON c.id = t.id 
                 AND t.name > c.last_name
)
SELECT id, combo FROM combinations ORDER BY id, level;

这段代码会生成id=1的所有子集：a、b、c、a,b、a,c、b,c、a,b,c。

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