【原】SQL 使用 PIVOT 和 UNPIVOT 行转列 列转行

DECLARE @products TABLE(
    City varchar(50),
    Product varchar(50),
    Price decimal
)

DECLARE @productPrice TABLE(
    City varchar(50),
    P1 decimal,
    P2 decimal,
    P3 decimal
)

INSERT INTO @products
SELECT 'Shanghai', 'P1', 50 UNION
SELECT 'Shanghai', 'P2', 100 UNION
SELECT 'Shanghai', 'P3', 150 UNION
SELECT 'Beijing', 'P1', 55 UNION
SELECT 'Beijing', 'P2', 105 UNION
SELECT 'Beijing', 'P3', 155 

SELECT
    T.City, T.P1, T.P2, T.P3
FROM
    @products 
pivot
(
    MAX(price) FOR Product IN ([P1], [P2], [P3])
) AS T


INSERT INTO @productPrice
SELECT
    T.City, T.P1, T.P2, T.P3
FROM
    @products 
pivot
(
    MAX(price) FOR Product IN ([P1], [P2], [P3])
) AS T

SELECT
    unpvt.City,
    unpvt.Product,
    unpvt.Price
FROM
    @productPrice
UNPIVOT
(
    Price FOR Product IN ([P1], [P2], [P3])
) AS unpvt