SQL Server 如何将不定字段数的多列数据合并为单列并生成连续ID

核心实现思路 #

核心是通过逆透视操作将同一行的多列值转为多行，过滤空值后按原表行顺序+列顺序排序，最后生成连续自增ID。

静态实现（列数已知场景） #

如果你的源表列数固定，可直接用以下代码，假设源表名为source_table：

SELECT
  ROW_NUMBER() OVER(ORDER BY t.ID, v.col_order) AS ID,
  v.col_val AS Col1
FROM source_table t
CROSS APPLY (
  VALUES
    (1, Col1),
    (2, Col2),
    (3, Col3) -- 有更多列直接在此补充即可
) AS v(col_order, col_val)
WHERE v.col_val IS NOT NULL;

动态实现（列数不固定场景，适配你的需求） #

如果源表列数不固定，不需要手动修改代码，直接通过系统视图动态获取列信息生成SQL：

DECLARE @sql NVARCHAR(MAX), @col_values NVARCHAR(MAX);

-- 动态拼接所有需要合并的列，排除原表的ID列
SELECT @col_values = STRING_AGG(CONCAT('(', column_id, ', ', QUOTENAME(name), ')'), ',')
FROM sys.columns
WHERE 
  object_id = OBJECT_ID('source_table') -- 替换为你的源表名
  AND name <> 'ID' -- 排除不需要合并的原ID列
ORDER BY column_id;

-- 拼接最终执行的SQL
SET @sql = N'
SELECT
  ROW_NUMBER() OVER(ORDER BY t.ID, v.col_order) AS ID,
  v.col_val AS Col1
FROM source_table t
CROSS APPLY (
  VALUES ' + @col_values + N'
) AS v(col_order, col_val)
WHERE v.col_val IS NOT NULL;
';

-- 执行动态SQL
EXEC sp_executesql @sql;

用到的核心函数/方法说明 #

• CROSS APPLY：关联原表每行和拆分出来的多行值，灵活性远高于UNPIVOT语法
• VALUES子句：将同一行的多个列转为多行虚拟表，附带的列序号保证同原行的列按Col1到ColN的顺序排列
• ROW_NUMBER()窗口函数：按原表ID+列序号排序生成连续新ID，完全匹配你需要的输出顺序
• STRING_AGG：快速拼接动态列列表，SQL Server 2017及以上版本原生支持，适配列数不固定的场景
• sys.columns系统视图：动态读取源表的列名和列顺序，不需要提前知道列的数量和名称

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