SQL Server中PIVOT 和 UNPIVOT的使用

事情的起因

我直接写的简单暴力的解

SELECT * FROM Customers WHERE customer_id NOT IN (SELECT customer_id FROM Orders WHERE product_name='C') 
and customer_id  IN (SELECT customer_id FROM Orders WHERE product_name='A') 
and customer_id  IN (SELECT customer_id FROM Orders WHERE product_name='B') 
order by  customer_id

然后最优解

with cte as(
    select customer_id, [A], [B], [C], [D]
    from orders
    pivot (
        count(order_id) 
        for product_name in ([A], [B], [C], [D])
    ) as pt
)

select c.customer_id, c.customer_name
from cte
join customers c on c.customer_id = cte.customer_id
where cte.A >= 1 and cte.B >= 1 and cte.C = 0

关于PIVOT，有点一列转多列的意思,id列和roletype会消失

WITH cte AS(
SELECT * FROM dbo.WechatUser 
PIVOT(
COUNT(id) FOR RoleType IN([1],[2],[3],[4]) 
) AS pt
)

关于UNPIVOT，有点行转列的意思

SELECT emp,orders,a.Name,* FROM dbo.WechatUser
UNPIVOT(
orders FOR emp IN(id,RoleType)
) AS a

他们并不常用，一般情况下，知道有这样个东西就行

微软官方文档

微软官方文档