>我有一个表格,在品牌列中有任意数量的重复项,大小列中的选项数量有限:
Item Brand Size
1 BiGNRG AA
2 LongLife AAA
3 LongLife AAA
4 BiGNRG AA
5 LongLife D
6 EcoBatt C
7 BiGNRG AAA
8 BiGNRG AA
9 EcoBatt C
我需要一个SQL查询将该信息转换为以下内容:
Brand AA AAA C D
BiGNRG 3 1 0 0
EcoBatt 0 0 2 0
LongLife 0 2 0 1
我不知道如何将这些数据转换为这种格式。
如果选项的数量有限,您可以尝试使用按un选择联合的组
select brand, count(AA) AA, count(AAA) AAA, count(C) C, count(D) D
from (
select Brand, 'AA' AA , NULL AAA, NULL C , NULL D
from my_table
where size ='AA'
union ALL
select Brand, null , 'AAA' , NULL , NULL
from my_table
where size ='AAA'
union ALL
select Brand, null , null , 'C' , NULL
from my_table
where size ='C'
union ALL
select Brand, null , null , null, 'D'
from my_table
where size ='D'
) T
group by brand