SQL实现Pets表行转列后关联Customers表输出指定结果的方法是什么？

这个需求属于SQL行转列（透视表）场景，以下是不同场景下的实现方案： #

通用标准SQL写法（兼容所有主流数据库） #

用条件聚合实现，不依赖数据库专属透视函数，假设Pets表中4类宠物类型分别为Cat、Dog、Bird、Fish，可替换为你实际的PetType取值：

SELECT 
    c.CustID,
    c.CustName,
    MAX(CASE WHEN c.PetType = 'Cat' THEN 'Yes' ELSE '' END) AS Cat,
    MAX(CASE WHEN c.PetType = 'Dog' THEN 'Yes' ELSE '' END) AS Dog,
    MAX(CASE WHEN c.PetType = 'Bird' THEN 'Yes' ELSE '' END) AS Bird,
    MAX(CASE WHEN c.PetType = 'Fish' THEN 'Yes' ELSE '' END) AS Fish
FROM Customers c
LEFT JOIN Pets p ON c.PetType = p.PetType
GROUP BY c.CustID, c.CustName
ORDER BY c.CustID;

说明：

• LEFT JOIN关联Pets表是为了过滤Customers表中不合法的PetType取值，即不在Pets表中的宠物类型不会被统计，若业务允许Customers表的PetType不在Pets表中，可去掉JOIN逻辑
• 即使一个客户持有多个同类型宠物，也只会标记一次Yes，符合输出要求

动态行转列写法（无需提前知道宠物类型） #

如果Pets表的宠物类型会频繁新增，不想手动修改SQL，可使用存储过程拼接动态SQL实现，以下是MySQL示例：

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN c.PetType = ''', PetType, ''' THEN ''Yes'' ELSE '''' END) AS `', PetType, '`')
) INTO @sql
FROM Pets;

SET @sql = CONCAT('SELECT c.CustID, c.CustName, ', @sql, ' 
                  FROM Customers c
                  LEFT JOIN Pets p ON c.PetType = p.PetType
                  GROUP BY c.CustID, c.CustName
                  ORDER BY c.CustID');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

主流数据库专属写法 #

SQL Server 用PIVOT函数实现 #

SELECT CustID, CustName, ISNULL(Cat, '') AS Cat, ISNULL(Dog, '') AS Dog, ISNULL(Bird, '') AS Bird, ISNULL(Fish, '') AS Fish
FROM (
    SELECT c.CustID, c.CustName, c.PetType, 'Yes' AS val
    FROM Customers c
    LEFT JOIN Pets p ON c.PetType = p.PetType
) AS src
PIVOT (
    MAX(val) FOR PetType IN (Cat, Dog, Bird, Fish)
) AS pvt
ORDER BY CustID;

PostgreSQL 用crosstab函数实现 #

SELECT * FROM crosstab(
    'SELECT c.CustID, c.CustName, p.PetType, ''Yes'' AS flag
     FROM Customers c
     LEFT JOIN Pets p ON c.PetType = p.PetType
     ORDER BY c.CustID',
    'SELECT DISTINCT PetType FROM Pets ORDER BY PetType'
) AS ct (CustID INT, CustName VARCHAR(100), Cat VARCHAR(3), Dog VARCHAR(3), Bird VARCHAR(3), Fish VARCHAR(3));

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