如何在SQL Server中使用PIVOT实现多列行转列操作

你可以通过两次PIVOT完成需求，也可以使用更易理解的条件聚合语法实现，两种方法如下：

方法1：两次PIVOT实现 #

-- 先将你的原始查询结果放到CTE中，替换下面的原始查询逻辑
WITH OriginalData AS (
    SELECT VendorId, Category, FirstSaleDate, StoreId
    FROM 你的原表名 -- 这里替换成你原来的查询语句
)
SELECT 
    VendorId,
    Car = MAX(Car),
    StoreId_car = MAX(StoreId_car),
    Clothes = MAX(Clothes),
    StoreId_clothes = MAX(StoreId_clothes),
    Toys = MAX(Toys),
    StoreId_toys = MAX(StoreId_toys),
    Food = MAX(Food),
    StoreId_food = MAX(StoreId_food),
    Others = MAX(Others),
    StoreId_others = MAX(StoreId_others)
FROM (
    -- 第一次PIVOT转换FirstSaleDate字段
    SELECT 
        VendorId,
        Car, Clothes, Toys, Food, Others,
        -- 给StoreId拼接对应分类后缀，用于第二次PIVOT
        StoreId_col = 'StoreId_' + LOWER(Category),
        StoreId
    FROM OriginalData
    PIVOT (
        MAX(FirstSaleDate)
        FOR Category IN (Car, Clothes, Toys, Food, Others)
    ) AS PivotFirstSale
) AS TempData
-- 第二次PIVOT转换StoreId字段
PIVOT (
    MAX(StoreId)
    FOR StoreId_col IN (StoreId_car, StoreId_clothes, StoreId_toys, StoreId_food, StoreId_others)
) AS PivotStoreId
GROUP BY VendorId

方法2：条件聚合实现（更适合新手理解，性能和PIVOT一致） #

SELECT
    VendorId,
    Car = MAX(CASE WHEN Category = 'Car' THEN FirstSaleDate END),
    StoreId_car = MAX(CASE WHEN Category = 'Car' THEN StoreId END),
    Clothes = MAX(CASE WHEN Category = 'Clothes' THEN FirstSaleDate END),
    StoreId_clothes = MAX(CASE WHEN Category = 'Clothes' THEN StoreId END),
    Toys = MAX(CASE WHEN Category = 'Toys' THEN FirstSaleDate END),
    StoreId_toys = MAX(CASE WHEN Category = 'Toys' THEN StoreId END),
    Food = MAX(CASE WHEN Category = 'Food' THEN FirstSaleDate END),
    StoreId_food = MAX(CASE WHEN Category = 'Food' THEN StoreId END),
    Others = MAX(CASE WHEN Category = 'Others' THEN FirstSaleDate END),
    StoreId_others = MAX(CASE WHEN Category = 'Others' THEN StoreId END)
FROM 你的原表名 -- 替换成你的原始查询语句
GROUP BY VendorId

说明 #

• 如果你的原始数据分类值不固定，可以动态拼接SQL语句生成列名，避免硬编码
• 两种方法都默认同一个VendorId下同一个Category只有一条记录，如果你有重复的分类记录，可以根据业务需求把MAX替换为MIN或者其他匹配的聚合函数

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