Pivot可以使sql表中多行旋转为列,UnPivot则使多列旋转为多行
有两个表Customers和Orders,内容如下:
现想找出每个城市中,订单数为0和非0的个数,可使用如下sql,使用Pivot:
以下sql,没有使用Pivot,和上边使用Pivot的情况等价:
有两个表Customers和Orders,内容如下:
select * from Customers
--结果如下
CustomerId, City
FISSA Madrid
FRNDO Madrid
KRLOS Madrid
MRPHS ZION
select * from Orders
--结果如下
orderid, customerid
1 FRNDO
2 FRNDO
3 KRLOS
4 KRLOS
5 KRLOS
6 MRPHS
7 NULL
现想找出每个城市中,订单数为0和非0的个数,可使用如下sql,使用Pivot:
select city,zeroorder,haveorder from
(select c.customerid,c.city,
case when count(orderid)=0 then 'ZeroOrder'
when count(orderid)>0 then 'HaveOrder'
end as category
from customers c left outer join orders o on c.customerid = o.customerid
group by c.customerid,c.city) d
pivot(
count(customerid)
for category in ([ZeroOrder],[HaveOrder])
) as p
order by city desc
以下sql,没有使用Pivot,和上边使用Pivot的情况等价:
select city,
count(case when category = 'ZeroOrder' then customerid end) as zeroorder,
count(case when category = 'HaveOrder' then customerid end) as HaveOrder
from
(
select c.customerid,c.city,
case when count(orderid)=0 then 'ZeroOrder'
when count(orderid)>0 then 'HaveOrder'
end as category
from customers c left outer join orders o on c.customerid = o.customerid
group by c.customerid,c.city
)
d
group by city
order by city desc