SQL Server：如何循环遍历表列获取非空值，无需枚举列名

嘿，这个需求我太懂了！要不用硬编码列名来提取表ABC里prod1到prod15的非空值，动态SQL+循环是最直接的方案，我给你两种实用的实现方式：

方法一：固定循环次数（适合列数明确为1-15的场景） #

这种方法利用列名的规律（prodX_name/prodX_percentage），直接循环1到15生成SQL语句，代码简洁直观：

DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @i INT = 1;

WHILE @i <= 15
BEGIN
    -- 拼接当前prodX列的查询语句
    SET @sql += N'
    SELECT 
        prod_name = ' + QUOTENAME('prod' + CAST(@i AS VARCHAR(2)) + '_name') + N',
        prod_percentage = ' + QUOTENAME('prod' + CAST(@i AS VARCHAR(2)) + '_percentage') + N'
    FROM ABC
    WHERE ' + QUOTENAME('prod' + CAST(@i AS VARCHAR(2)) + '_name') + N' IS NOT NULL 
        AND ' + QUOTENAME('prod' + CAST(@i AS VARCHAR(2)) + '_percentage') + N' IS NOT NULL'
    
    -- 最后一次循环不加UNION ALL
    IF @i < 15
        SET @sql += N' UNION ALL ';
    
    SET @i += 1;
END

-- 执行生成好的动态SQL
EXEC sp_executesql @sql;

代码说明： #

• QUOTENAME函数用来包裹列名，避免列名含特殊字符导致语法错误
• WHERE条件过滤掉name和percentage都为空的记录，如果需求是只要其中一个非空就保留，把AND改成OR即可
• 循环自动遍历1-15的所有列对，不用手动逐个枚举

方法二：基于系统视图自动识别列（适合列数不固定的通用场景） #

如果以后列数可能变化（比如新增prod16），用系统视图INFORMATION_SCHEMA.COLUMNS自动获取所有prodX_name列，再匹配对应的percentage列，扩展性更强：

DECLARE @sql NVARCHAR(MAX) = N'';

WITH ProdColumns AS (
    SELECT 
        ColumnName = c.COLUMN_NAME,
        -- 提取prod后面的数字序号
        ProdNumber = CAST(SUBSTRING(c.COLUMN_NAME, 5, CHARINDEX('_', c.COLUMN_NAME) - 5) AS INT)
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE c.TABLE_NAME = 'ABC'
        AND c.COLUMN_NAME LIKE 'prod%_name'
)
SELECT @sql += N'
SELECT 
    prod_name = ' + QUOTENAME(p.ColumnName) + N',
    prod_percentage = ' + QUOTENAME(REPLACE(p.ColumnName, '_name', '_percentage')) + N'
FROM ABC
WHERE ' + QUOTENAME(p.ColumnName) + N' IS NOT NULL 
    AND ' + QUOTENAME(REPLACE(p.ColumnName, '_name', '_percentage')) + N' IS NOT NULL' + 
    -- 给除了最后一行的结果加UNION ALL
    CASE WHEN LEAD(p.ColumnName) OVER (ORDER BY p.ProdNumber) IS NOT NULL THEN N' UNION ALL ' ELSE N'' END
FROM ProdColumns p
ORDER BY p.ProdNumber;

EXEC sp_executesql @sql;

代码说明： #

• CTEProdColumns从系统表中筛选出所有prodX_name列，并提取序号用于排序
• REPLACE函数自动把prodX_name转换成对应的prodX_percentage列名
• LEAD函数判断是否是最后一列，自动添加UNION ALL

两种方法都能完美实现你的需求，不用手动写15次列名，根据你的实际场景选就行～

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