如何用动态SQL按列前缀对SQL表执行Unpivot操作

现有从JSON文档错误导入的SQL表，结构如下：

X1AB_name     X1AB_age        Y2AL_name       Y2AL_age
"Todd"        10              "Brad"          20

需要按列名中下划线前的前缀进行逆透视（Unpivot），期望得到结果：

id              name         age
"X1AB"          "Todd"        10
"Y2AL"          "Brad"        20

是否可以通过动态SQL实现？

可以用动态SQL实现，核心思路是先提取所有列名的前缀（下划线前的部分），再针对每个前缀拼接对应的name和age列，最后组合成逆透视查询语句。

以下是SQL Server环境下的实现代码：

DECLARE @sql NVARCHAR(MAX) = '';

-- 提取所有唯一的前缀（下划线前的部分）
WITH ColumnPrefixes AS (
    SELECT DISTINCT 
        LEFT(name, CHARINDEX('_', name) - 1) AS Prefix
    FROM sys.columns 
    WHERE object_id = OBJECT_ID('YourTableName') -- 替换为你的实际表名
      AND (name LIKE '%[_]name' OR name LIKE '%[_]age')
)
-- 拼接每个前缀对应的查询片段
SELECT @sql += N'
SELECT 
    ''' + Prefix + ''' AS id,
    ' + QUOTENAME(Prefix + '_name') + ' AS name,
    ' + QUOTENAME(Prefix + '_age') + ' AS age
FROM YourTableName -- 替换为你的实际表名
UNION ALL'
FROM ColumnPrefixes;

-- 移除末尾多余的UNION ALL
SET @sql = LEFT(@sql, LEN(@sql) - LEN('UNION ALL'));

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

注意事项 #

• 务必将代码中的YourTableName替换为你的目标表名
• 代码会自动识别所有符合前缀_name和前缀_age格式的列，无需手动指定前缀
• 若使用其他数据库（如MySQL），需调整系统表和字符串函数：比如用INFORMATION_SCHEMA.COLUMNS替代sys.columns，用SUBSTRING_INDEX(name, '_', 1)提取前缀

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