如何在单查询中对大量非空列动态拼接列名与对应值？

搞定批量列的非空值拼接其实不难，我给你分两种场景来解决——一种是列固定但数量多的静态场景，另一种是列数不确定需要自动适配的动态场景，直接上代码和解释：

如果你的表结构固定，只是列数很多，直接用CONCAT_WS函数就能轻松搞定。CONCAT_WS会自动忽略NULL值，只拼接非空的列项，每个项我们用CASE判断：当列值非空时，把列名和对应值组合成"列名: 值"的格式。

CREATE TABLE Table1 ([C1] varchar(50), [C2] varchar(50), [C3] varchar(50),[C4] varchar(50),[C5] varchar(50) ) ;
INSERT INTO Table1 ([C1], [C2], [C3], [C4], [C5]) VALUES 
('F92', 'title',null,null,'B350'), 
('F92', 'title',null,null,'B150'), 
('F92', 'title',null,null,'PB100') ;

-- 拼接所有非空列，结果用逗号分隔
SELECT 
  CONCAT_WS(', ',
    CASE WHEN C1 IS NOT NULL THEN 'C1: ' + C1 END,
    CASE WHEN C2 IS NOT NULL THEN 'C2: ' + C2 END,
    CASE WHEN C3 IS NOT NULL THEN 'C3: ' + C3 END,
    CASE WHEN C4 IS NOT NULL THEN 'C4: ' + C4 END,
    CASE WHEN C5 IS NOT NULL THEN 'C5: ' + C5 END
  ) AS CombinedResult
FROM Table1;

DROP TABLE Table1;

执行后会得到这样的结果：

C1: F92, C2: title, C5: B350
C1: F92, C2: title, C5: B150
C1: F92, C2: title, C5: PB100

完全符合你要的“非空列才拼接”的需求，而且不管列有多少，只要按这个格式把每个列的CASE语句加进去就行。

如果你的表列数很多，或者表结构可能变化，不想手动写每一列的CASE语句，那就用动态SQL自动生成拼接逻辑。我们可以通过系统视图sys.columns获取目标表的所有列名，然后自动拼接成完整的查询语句。

适用于SQL Server 2017+（支持STRING_AGG） #

CREATE TABLE Table1 ([C1] varchar(50), [C2] varchar(50), [C3] varchar(50),[C4] varchar(50),[C5] varchar(50) ) ;
INSERT INTO Table1 ([C1], [C2], [C3], [C4], [C5]) VALUES 
('F92', 'title',null,null,'B350'), 
('F92', 'title',null,null,'B150'), 
('F92', 'title',null,null,'PB100') ;

DECLARE @TableName NVARCHAR(128) = 'Table1'; -- 替换成你的表名
DECLARE @SQL NVARCHAR(MAX);

-- 自动生成所有列的CASE语句
SELECT @SQL = STRING_AGG(
  CONCAT('CASE WHEN ', QUOTENAME(name), ' IS NOT NULL THEN ''', name, ': '' + ', QUOTENAME(name), ' END'),
  ', '
)
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName);

-- 组合成完整的SELECT查询
SET @SQL = CONCAT(
  'SELECT CONCAT_WS('', '', ', @SQL, ') AS CombinedResult FROM ', @TableName
);

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

DROP TABLE Table1;

兼容SQL Server 2016及以下版本 #

如果你的SQL Server版本不支持STRING_AGG，可以用FOR XML PATH来拼接列的CASE语句：

CREATE TABLE Table1 ([C1] varchar(50), [C2] varchar(50), [C3] varchar(50),[C4] varchar(50),[C5] varchar(50) ) ;
INSERT INTO Table1 ([C1], [C2], [C3], [C4], [C5]) VALUES 
('F92', 'title',null,null,'B350'), 
('F92', 'title',null,null,'B150'), 
('F92', 'title',null,null,'PB100') ;

DECLARE @TableName NVARCHAR(128) = 'Table1';
DECLARE @SQL NVARCHAR(MAX);

-- 用FOR XML PATH拼接CASE语句
SELECT @SQL = COALESCE(@SQL + ', ', '') + 
  CONCAT('CASE WHEN ', QUOTENAME(name), ' IS NOT NULL THEN ''', name, ': '' + ', QUOTENAME(name), ' END')
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName);

SET @SQL = CONCAT(
  'SELECT CONCAT_WS('', '', ', @SQL, ') AS CombinedResult FROM ', @TableName
);

EXEC sp_executesql @SQL;

DROP TABLE Table1;

这个方案的好处是，不管你的表有10列还是100列，都能自动适配，不用手动修改代码，非常适合大量列的场景。

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