SQL存储过程动态列查询需求求助：按指定列名提取数据

嘿，刚接触存储过程的话，这个动态列提取的需求确实有点考验人，但咱们一步步拆解来实现它～

核心思路 #

因为#table1里的colname列是动态的（可能是单个列，也可能是多个逗号分隔的列），静态SQL没法提前预知要提取哪些列，所以得用动态SQL来逐行处理，把对应列的值拼接后插入到#table2中。

完整实现代码（SQL Server环境） #

-- 先创建目标临时表#table2，匹配期望的结构
CREATE TABLE #table2 (
    NewId INT,
    colName VARCHAR(100),
    [Values] VARCHAR(MAX) -- Values是SQL关键字，用方括号包裹避免语法错误
)

-- 声明变量存储游标遍历的每一行数据
DECLARE @Id INT, @colname VARCHAR(100), @dynamicSql NVARCHAR(MAX)

-- 定义游标，遍历#table1的每一条记录
DECLARE dataCursor CURSOR FOR
SELECT Id, colname FROM #table1

OPEN dataCursor
FETCH NEXT FROM dataCursor INTO @Id, @colname

-- 逐行处理每一条记录
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 生成动态SQL：根据当前行的colname，拼接对应列的值
    -- 用STUFF+FOR XML PATH实现多列值的逗号拼接，兼容SQL Server 2016及以下版本
    SET @dynamicSql = N'
        INSERT INTO #table2 (NewId, colName, [Values])
        SELECT 
            ' + CAST(@Id AS VARCHAR(10)) + N',
            ''' + REPLACE(@colname, '''', '''''') + N''', -- 转义单引号避免语法错误
            STUFF((
                SELECT '', '' + CAST('' + REPLACE(''' + @colname + N''', '', '', '', '') + '' AS VARCHAR(MAX))
                FROM #table1 t
                WHERE t.Id = ' + CAST(@Id AS VARCHAR(10)) + N'
                FOR XML PATH(''''), TYPE
            ).value(''.'', ''VARCHAR(MAX)''), 1, 2, '''') -- 去掉开头多余的", "
    '

    -- 执行动态SQL
    EXEC sp_executesql @dynamicSql

    -- 取下一行数据
    FETCH NEXT FROM dataCursor INTO @Id, @colname
END

-- 关闭并释放游标
CLOSE dataCursor
DEALLOCATE dataCursor

-- 查看最终结果
SELECT * FROM #table2

关键点说明 #

1. 游标遍历：用游标逐行处理#table1的记录，因为每一行的colname可能不同，需要单独生成对应的提取逻辑。
2. 动态SQL拼接：根据当前行的colname，动态拼接要提取的列，并把对应的值用逗号连接起来。
3. 转义处理：用REPLACE(@colname, '''', '''''')转义单引号，避免动态SQL出现语法错误或注入风险。
4. 高版本简化方案：如果你的SQL Server是2017及以上版本，可以用STRING_AGG替代STUFF+FOR XML PATH，代码会更简洁：

-- 高版本简化版的动态SQL部分
SET @dynamicSql = N'
    INSERT INTO #table2 (NewId, colName, [Values])
    SELECT 
        ' + CAST(@Id AS VARCHAR(10)) + N',
        ''' + REPLACE(@colname, '''', '''''') + N''',
        STRING_AGG(CAST(t.col AS VARCHAR(MAX)), '', '')
    FROM #table1 t
    CROSS APPLY STRING_SPLIT(''' + REPLACE(@colname, '''', '''''') + N''', '', '') s
    CROSS APPLY (SELECT t.' + QUOTENAME(s.value) + ' AS col) c
    WHERE t.Id = ' + CAST(@Id AS VARCHAR(10)) + N'
'

测试提示 #

运行前请确保#table1已经创建并填充了示例数据，执行完存储过程后，查询#table2就能得到你期望的结果啦～

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