如何将ProductFile表分组行转换为列展示？SQL技术求助

现有查询及结果 #

当前使用的SQL语句：

SELECT ProductId,LanguageId,FileType 
FROM dbo.ProductFile
GROUP BY ProductId,FileType,LanguageId

查询返回结果：

期望目标结果 #

将每个产品按语言聚合为一行，每种文件类型作为独立列，标记是否存在：

实现方案 #

方案一：条件聚合（兼容性强，适合已知文件类型的场景） #

利用CASE表达式配合聚合函数，直接按产品和语言分组后判断每种文件类型是否存在：

SELECT 
    ProductId AS Code,
    LanguageId AS Language,
    CASE WHEN COUNT(CASE WHEN FileType = 'manual' THEN 1 END) > 0 THEN 'true' ELSE 'false' END AS manual,
    CASE WHEN COUNT(CASE WHEN FileType = 'exploded' THEN 1 END) > 0 THEN 'true' ELSE 'false' END AS exploded,
    CASE WHEN COUNT(CASE WHEN FileType = 'catalog' THEN 1 END) > 0 THEN 'true' ELSE 'false' END AS catalog
FROM dbo.ProductFile
GROUP BY ProductId, LanguageId
ORDER BY ProductId, LanguageId;

方案二：PIVOT函数（SQL Server专属，语法更简洁） #

通过子查询标记存在的文件类型，再用PIVOT行转列，最后用ISNULL处理不存在的情况：

SELECT 
    ProductId AS Code,
    LanguageId AS Language,
    ISNULL(manual, 'false') AS manual,
    ISNULL(exploded, 'false') AS exploded,
    ISNULL(catalog, 'false') AS catalog
FROM (
    SELECT ProductId, LanguageId, FileType, 'true' AS HasFile
    FROM dbo.ProductFile
) AS SourceTable
PIVOT (
    MAX(HasFile)
    FOR FileType IN ([manual], [exploded], [catalog])
) AS PivotTable
ORDER BY ProductId, LanguageId;

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