如何编写SQL查询将复合索引的所有列合并为单行返回？

当通过sys.indexes和sys.index_columns关联查询复合索引时，默认会把每个索引列拆分为单独的行返回。要把同一索引的所有列合并成单行，用逗号分隔显示，可以根据你使用的SQL Server版本选择以下两种方法：

方法1：使用STRING_AGG（SQL Server 2017及以上版本） #

这是最简单高效的方式，SQL Server 2017引入的STRING_AGG函数专门用于字符串聚合：

SELECT 
    i.name AS index_name,
    STRING_AGG(COL_NAME(ic.object_id, ic.column_id), ',') AS column_names
FROM 
    sys.indexes AS i
INNER JOIN 
    sys.index_columns AS ic ON i.object_id = ic.object_id 
                            AND i.index_id = ic.index_id
WHERE 
    i.object_id = OBJECT_ID('#t')
GROUP BY 
    i.object_id, i.name, i.index_id;

方法2：使用FOR XML PATH（兼容SQL Server 2016及更早版本） #

如果你的版本不支持STRING_AGG，可以用FOR XML PATH的方式来拼接字符串：

SELECT 
    DISTINCT i.name AS index_name,
    STUFF(
        (
            SELECT ',' + COL_NAME(ic2.object_id, ic2.column_id)
            FROM sys.index_columns ic2
            WHERE ic2.object_id = i.object_id 
              AND ic2.index_id = i.index_id
            ORDER BY ic2.key_ordinal
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1, 1, ''
    ) AS column_names
FROM 
    sys.indexes AS i
INNER JOIN 
    sys.index_columns AS ic ON i.object_id = ic.object_id 
                            AND i.index_id = ic.index_id
WHERE 
    i.object_id = OBJECT_ID('#t');

验证示例 #

先创建测试环境：

CREATE TABLE #t (id INT, d VARCHAR(10), e INT);
CREATE INDEX idx_test ON #t(d, e);

执行上面的任意一种查询，都会得到如下结果：

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