SCCM数据SQL需求：按适配器类型行转列并合并MAC地址

问题场景 #

当前用于查询SCCMV_GS_NETWORK_ADAPTER表的SQL语句如下：

SELECT DISTINCT ResourceID
            ,   AdapterType0
            ,   MACAddress0

FROM    V_GS_NETWORK_ADAPTER 

WHERE   MACAddress0 is not null

查询结果中同一ResourceID对应多条适配器记录，需按AdapterType0实现行转列，将同一类型的MACAddress0合并拼接至对应列，期望输出格式如下：

尝试使用STUFF函数未得到预期结果，现提供可行解决方案。

实现方案 #

方案1：SQL Server 2017+ 版本（使用STRING_AGG+动态PIVOT） #

该方案利用STRING_AGG简化MAC地址合并，再通过动态PIVOT自动适配所有适配器类型：

-- 第一步：合并同一ResourceID和AdapterType下的MAC地址
WITH AggregatedData AS (
    SELECT 
        ResourceID,
        AdapterType0,
        CONCAT(AdapterType0, ': ', STRING_AGG(MACAddress0, '; ')) AS AdapterMAC
    FROM V_GS_NETWORK_ADAPTER
    WHERE MACAddress0 IS NOT NULL
    GROUP BY ResourceID, AdapterType0
),
-- 第二步：生成适配器类型对应的列名（如AdapterType0、AdapterType1）
AdapterTypes AS (
    SELECT DISTINCT 
        'AdapterType' + CAST(ROW_NUMBER() OVER (ORDER BY AdapterType0) AS VARCHAR(10)) AS ColumnName,
        AdapterType0
    FROM V_GS_NETWORK_ADAPTER
    WHERE MACAddress0 IS NOT NULL
)
-- 第三步：动态构建PIVOT查询语句
DECLARE @PivotColumns NVARCHAR(MAX), @SQL NVARCHAR(MAX)
SELECT @PivotColumns = STRING_AGG(QUOTENAME(ColumnName), ', ')
FROM AdapterTypes

SET @SQL = N'
SELECT ResourceID, ' + @PivotColumns + N'
FROM (
    SELECT 
        ad.ResourceID,
        at.ColumnName,
        ad.AdapterMAC
    FROM AggregatedData ad
    JOIN AdapterTypes at ON ad.AdapterType0 = at.AdapterType0
) AS PivotSource
PIVOT (
    MAX(AdapterMAC)
    FOR ColumnName IN (' + @PivotColumns + N')
) AS PivotResult
'

EXEC sp_executesql @SQL

方案2：兼容SQL Server 2016及以下版本（使用STUFF+FOR XML PATH+动态PIVOT） #

针对低版本SQL Server，用STUFF+FOR XML PATH替代STRING_AGG实现MAC地址合并：

-- 第一步：合并同一ResourceID和AdapterType下的MAC地址
WITH AggregatedData AS (
    SELECT 
        ResourceID,
        AdapterType0,
        CONCAT(AdapterType0, ': ', STUFF((
            SELECT '; ' + MACAddress0
            FROM V_GS_NETWORK_ADAPTER t2
            WHERE t2.ResourceID = t1.ResourceID AND t2.AdapterType0 = t1.AdapterType0
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')) AS AdapterMAC
    FROM V_GS_NETWORK_ADAPTER t1
    WHERE MACAddress0 IS NOT NULL
    GROUP BY ResourceID, AdapterType0
),
-- 第二步：生成适配器类型对应的列名（如AdapterType0、AdapterType1）
AdapterTypes AS (
    SELECT DISTINCT 
        'AdapterType' + CAST(ROW_NUMBER() OVER (ORDER BY AdapterType0) AS VARCHAR(10)) AS ColumnName,
        AdapterType0
    FROM V_GS_NETWORK_ADAPTER
    WHERE MACAddress0 IS NOT NULL
)
-- 第三步：动态构建PIVOT查询语句
DECLARE @PivotColumns NVARCHAR(MAX), @SQL NVARCHAR(MAX)
SELECT @PivotColumns = STUFF((
    SELECT ', ' + QUOTENAME(ColumnName)
    FROM AdapterTypes
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

SET @SQL = N'
SELECT ResourceID, ' + @PivotColumns + N'
FROM (
    SELECT 
        ad.ResourceID,
        at.ColumnName,
        ad.AdapterMAC
    FROM AggregatedData ad
    JOIN AdapterTypes at ON ad.AdapterType0 = at.AdapterType0
) AS PivotSource
PIVOT (
    MAX(AdapterMAC)
    FOR ColumnName IN (' + @PivotColumns + N')
) AS PivotResult
'

EXEC sp_executesql @SQL

关键说明 #

• 动态PIVOT会自动根据系统中实际存在的适配器类型生成对应的AdapterTypeN列，无需手动预定义列数
• 合并后的内容格式为[适配器类型]: [MAC1]; [MAC2]，完全匹配需求输出
• 若需固定列数（如最多显示3种适配器类型），可修改AdapterTypes中的ROW_NUMBER()逻辑，或直接手动指定PIVOT列名

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