sql - 如何在sql中进行转换和旋转

Question

我有一张如下表

我想计算每个产品并对其进行改造

t1

customer product
A            a
A            b
A            c 
B            a
B            c
C            a

我想要的结果如下，

customer  product(a) product(b) product(c)
A           1           1           1
B           1           0           1
C           1           0           0

有什么办法可以变身？？

谢谢

标签： sqloracle

Answer 1

您可以使用conditional aggregation

select customer,count(case when product='a' then 1 end) as product_a,
count(case when product='b' then 1 end) as product_b,
count(case when product='c' then 1 end) as product_c
from tablename
group by customer

另一种选择可能是使用pivot operator-

SELECT * FROM t
 PIVOT (count(product) FOR product IN 
    ( 'a' AS product_a, 'b' AS  product_b, 'c' AS product_c ))