PostgreSQL：如何将行内多列转换为单列多行分组展示

嘿，要实现你需要的这种多列转单列分组的效果，PostgreSQL里有个很方便的办法，我给你详细拆解一下：

首先，最基础的需求是把每行的多个列值（比如cidade和grupo）拆成Grouping列的单独行，这时候用UNNEST()函数结合数组就能轻松搞定：

SELECT UNNEST(ARRAY[cidade, grupo]) AS Grouping
FROM dados;

执行这段SQL后，你会得到这样的结果：

如果要完全贴合你给出的目标格式——每个原行的两个值之后加一条分隔线，我们可以借助行号来实现：

WITH ranked_data AS (
    -- 给原表每行分配唯一行号，方便后续分组排序
    SELECT 
        cidade, 
        grupo,
        ROW_NUMBER() OVER () AS row_num
    FROM dados
),
expanded_rows AS (
    -- 把每个列拆成单独行，同时生成分隔行
    SELECT row_num, cidade AS Grouping FROM ranked_data
    UNION ALL
    SELECT row_num, grupo AS Grouping FROM ranked_data
    UNION ALL
    SELECT row_num, '---------' AS Grouping FROM ranked_data
)
-- 按行号排序，确保分隔行在每个原行的两个值之后
SELECT Grouping
FROM expanded_rows
ORDER BY row_num, 
         CASE Grouping 
             WHEN '---------' THEN 3  -- 让分隔行排在最后
             ELSE 1
         END;

运行这个查询后，输出就和你想要的格式完全一致了：

如果你的实际需求是处理3列（比如再加一个outra_coluna），只需要简单调整代码即可：

-- 基础无分隔版
SELECT UNNEST(ARRAY[cidade, grupo, outra_coluna]) AS Grouping
FROM dados;

-- 带分隔版
WITH ranked_data AS (
    SELECT 
        cidade, 
        grupo,
        outra_coluna,
        ROW_NUMBER() OVER () AS row_num
    FROM dados
),
expanded_rows AS (
    SELECT row_num, cidade AS Grouping FROM ranked_data
    UNION ALL
    SELECT row_num, grupo AS Grouping FROM ranked_data
    UNION ALL
    SELECT row_num, outra_coluna AS Grouping FROM ranked_data
    UNION ALL
    SELECT row_num, '---------' AS Grouping FROM ranked_data
)
SELECT Grouping
FROM expanded_rows
ORDER BY row_num, 
         CASE Grouping 
             WHEN '---------' THEN 4
             ELSE 1
         END;

这样不管是2列还是3列，都能完美实现你要的分组转单列效果~

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