如何将用户应用安装数据聚合为单行格式适配Excel筛选？

核心逻辑梳理 #

1. 先将IsManaged字段转换为安装状态标记：IsManaged='Yes' → Yes（已安装）；IsManaged='No'或无对应应用记录 → No/留空（未安装）
2. 通过**行转列（透视）**将每个应用转为单独列，实现单用户单行的格式

方案1：静态透视（已知应用列表） #

如果应用列表固定（比如仅Excel、Word、Chrome），直接用PIVOT函数即可实现：

示例代码 #

-- 假设原始表名为UserAppInstalls
SELECT 
    Username,
    -- COALESCE处理无记录的情况，留空未安装项则去掉第二个参数
    COALESCE(Excel, 'No') AS Excel,
    COALESCE(Word, 'No') AS Word,
    COALESCE(Chrome, 'No') AS Chrome
FROM (
    -- 预处理IsManaged为安装状态标记
    SELECT 
        Username,
        AppName,
        CASE WHEN IsManaged = 'Yes' THEN 'Yes' END AS InstallStatus
    FROM UserAppInstalls
) AS PivotSource
PIVOT (
    -- 取每个用户-应用的唯一状态（单用户单应用仅一条记录时，MAX/MIN均可）
    MAX(InstallStatus)
    FOR AppName IN ([Excel], [Word], [Chrome])
) AS PivotResult
ORDER BY Username;

关键说明 #

• 子查询的CASE逻辑：仅托管应用标记为Yes，未托管或无记录则返回NULL，后续通过COALESCE统一转为No，若需留空未安装项，直接删除COALESCE的第二个参数即可
• PIVOT的聚合函数：因单用户对单应用仅一条记录，用MAX/MIN仅为满足语法要求，实际是取唯一值

方案2：动态透视（应用列表不固定） #

若应用数量多且随时变化，静态透视维护成本高，用动态SQL自动生成应用列：

示例代码（SQL Server 2017+） #

DECLARE @AppColumns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- 生成所有应用的列名（带方括号避免关键字冲突）
SELECT @AppColumns = STRING_AGG(QUOTENAME(AppName), ', ')
FROM (SELECT DISTINCT AppName FROM UserAppInstalls) AS UniqueApps;

-- 拼接动态SQL语句
SET @SQL = N'
SELECT 
    Username,
    ' + STRING_AGG('COALESCE(' + QUOTENAME(AppName) + ', ''No'') AS ' + QUOTENAME(AppName), ', ') + '
FROM (
    SELECT 
        Username,
        AppName,
        CASE WHEN IsManaged = ''Yes'' THEN ''Yes'' END AS InstallStatus
    FROM UserAppInstalls
) AS PivotSource
PIVOT (
    MAX(InstallStatus)
    FOR AppName IN (' + @AppColumns + ')
) AS PivotResult
ORDER BY Username;';

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

兼容旧版本SQL Server（2016及以下） #

替换STRING_AGG部分，用FOR XML PATH拼接列名：

SELECT @AppColumns = STUFF((SELECT ', ' + QUOTENAME(AppName)
                            FROM (SELECT DISTINCT AppName FROM UserAppInstalls) AS UniqueApps
                            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

注意事项 #

• 确保Username是用户的唯一标识，若需关联设备，可将ID字段加入SELECT列表
• 若同一用户同一应用存在多条记录，子查询需先去重（如用DISTINCT或GROUP BY取最新的IsManaged状态）
• 导出至Excel后，直接使用Excel的筛选功能即可按应用安装状态筛选用户

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