如何将单列中同一分组的多值拆分至多列？SQL查询改写需求

可以通过SQL的行转列（Pivot）操作实现需求，以下是针对不同主流数据库的具体实现：

原表结构 #

目标表结构 #

1. MySQL/MariaDB（条件聚合实现） #

由于MySQL无原生PIVOT函数，采用分组+条件聚合的方式：

SELECT 
  CompanyLocationName,
  MAX(CASE WHEN rn = 1 THEN LineName END) AS LineName,
  MAX(CASE WHEN rn = 2 THEN LineName END) AS LineName2,
  MAX(CASE WHEN rn = 3 THEN LineName END) AS LineName3
FROM (
  SELECT 
    CompanyLocationName,
    LineName,
    ROW_NUMBER() OVER (PARTITION BY CompanyLocationName ORDER BY LineName) AS rn
  FROM your_table_name
) t
GROUP BY CompanyLocationName;

• 子查询通过ROW_NUMBER()为同一公司的LineName生成序号，按LineName排序保证列值顺序稳定
• 外层用MAX(CASE...)将不同序号的LineName映射到对应列，无匹配值时自动填充null

2. SQL Server/Azure SQL（原生PIVOT函数实现） #

利用SQL Server原生的PIVOT语法简化操作：

SELECT 
  CompanyLocationName,
  [1] AS LineName,
  [2] AS LineName2,
  [3] AS LineName3
FROM (
  SELECT 
    CompanyLocationName,
    LineName,
    ROW_NUMBER() OVER (PARTITION BY CompanyLocationName ORDER BY LineName) AS rn
  FROM your_table_name
) t
PIVOT (
  MAX(LineName) FOR rn IN ([1], [2], [3])
) p;

• 子查询生成行号后，通过PIVOT直接将行号维度转成列，缺失值自动填充null

3. PostgreSQL（两种实现方式） #

方法一：通用条件聚合 #

SELECT 
  CompanyLocationName,
  MAX(CASE WHEN rn = 1 THEN LineName END) AS LineName,
  MAX(CASE WHEN rn = 2 THEN LineName END) AS LineName2,
  MAX(CASE WHEN rn = 3 THEN LineName END) AS LineName3
FROM (
  SELECT 
    CompanyLocationName,
    LineName,
    ROW_NUMBER() OVER (PARTITION BY CompanyLocationName ORDER BY LineName) AS rn
  FROM your_table_name
) t
GROUP BY CompanyLocationName;

方法二：使用crosstab函数（需启用扩展） #

先启用tablefunc扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

再执行行转列查询：

SELECT 
  CompanyLocationName,
  col1 AS LineName,
  col2 AS LineName2,
  col3 AS LineName3
FROM crosstab(
  'SELECT CompanyLocationName, rn, LineName 
   FROM (
     SELECT 
       CompanyLocationName,
       LineName,
       ROW_NUMBER() OVER (PARTITION BY CompanyLocationName ORDER BY LineName) AS rn
     FROM your_table_name
   ) t
   ORDER BY 1,2',
  'SELECT generate_series(1,3)'
) AS ct(CompanyLocationName text, col1 text, col2 text, col3 text);

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