SQL Server 2016：将多行数据按不同迭代转换为列

嘿，这个需求就是典型的**行转列（Pivot）**场景，我给你整理了几个常用数据库的实现方案，直接套用就行～

先明确下需求的前后对比，方便你对应：

原表结构示例：

MemberID	InsuranceNo
1	INS001
1	INS002
2	INS003

期望结果：

MemberID	InsuranceNo1	InsuranceNo2
1	INS001	INS002
2	INS003	NULL

1. SQL Server 用内置 PIVOT 函数 #

SQL Server自带的PIVOT语法很适合这种场景，先给每个MemberID下的InsuranceNo编序号，再转成列：

-- 先给每个MemberID的InsuranceNo生成唯一序号
WITH RankedIns AS (
    SELECT 
        MemberID,
        InsuranceNo,
        -- 生成列名：InsuranceNo1、InsuranceNo2...
        'InsuranceNo' + CAST(ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY InsuranceNo) AS VARCHAR(10)) AS ColName
    FROM YourTableName -- 替换成你的表名
)
-- 用PIVOT转成列格式
SELECT 
    MemberID,
    -- 用ISNULL把空值换成你需要的默认值，比如空字符串
    ISNULL([InsuranceNo1], '') AS InsuranceNo1,
    ISNULL([InsuranceNo2], '') AS InsuranceNo2,
    ISNULL([InsuranceNo3], '') AS InsuranceNo3 -- 按需增加更多列
FROM RankedIns
PIVOT (
    MAX(InsuranceNo) -- 聚合函数选MAX/ MIN都可以，因为每个序号对应唯一值
    FOR ColName IN ([InsuranceNo1], [InsuranceNo2], [InsuranceNo3])
) AS PivotTable;

如果不确定最多有多少个InsuranceNo，可以用动态SQL自动生成列，避免手动加列的麻烦。

2. MySQL 两种实现方式 #

MySQL没有内置PIVOT，不过可以用字符串拼接拆分，或者动态SQL来搞定：

方法一：静态列（已知最多InsuranceNo数量） #

适合你能确定每个MemberID最多有几个InsuranceNo的情况：

SELECT 
    MemberID,
    -- 拆分出第1个InsuranceNo
    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(InsuranceNo ORDER BY InsuranceNo), ',', 1), ',', -1) AS InsuranceNo1,
    -- 拆分出第2个InsuranceNo
    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(InsuranceNo ORDER BY InsuranceNo), ',', 2), ',', -1) AS InsuranceNo2,
    -- 按需增加更多列
    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(InsuranceNo ORDER BY InsuranceNo), ',', 3), ',', -1) AS InsuranceNo3
FROM YourTableName
GROUP BY MemberID;

原理是先用GROUP_CONCAT把同一个MemberID的InsuranceNo拼成逗号分隔的字符串，再用SUBSTRING_INDEX拆分出对应位置的值。

方法二：动态SQL（自动适配最多列数） #

如果InsuranceNo的数量不确定，用动态SQL自动生成所有需要的列：

SET @sql = NULL;
-- 生成所有拆分列的SQL语句
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(InsuranceNo ORDER BY InsuranceNo), '','', ', n, '), '','', -1) AS InsuranceNo', n
    )
  ) INTO @sql
FROM (
  -- 找出最多有多少个InsuranceNo，生成对应的序号
  SELECT ROW_NUMBER() OVER () AS n
  FROM YourTableName
  GROUP BY MemberID
  HAVING COUNT(*) = (SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM YourTableName GROUP BY MemberID) t)
) t;

-- 拼接完整的查询语句
SET @sql = CONCAT('SELECT MemberID, ', @sql, ' FROM YourTableName GROUP BY MemberID');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

3. PostgreSQL 用 crosstab 函数 #

PostgreSQL需要先启用tablefunc扩展，然后用crosstab实现行转列：

-- 先启用tablefunc扩展（如果还没启用）
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 执行行转列查询
SELECT 
    MemberID,
    "1" AS InsuranceNo1,
    "2" AS InsuranceNo2,
    "3" AS InsuranceNo3 -- 按需增加列
FROM crosstab(
    -- 第一个参数：带序号的源数据查询
    'SELECT MemberID, ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY InsuranceNo), InsuranceNo FROM YourTableName ORDER BY 1,2',
    -- 第二个参数：列的序号范围（这里的3替换成实际最多的InsuranceNo数量）
    'SELECT generate_series(1,3)'
) AS ct(MemberID INT, InsuranceNo1 VARCHAR(50), InsuranceNo2 VARCHAR(50), InsuranceNo3 VARCHAR(50));

小提示：如果你的业务中InsuranceNo的数量经常变化，优先选动态SQL方案；如果数量固定，静态查询更简单高效～

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