将行显示为服装产品的列

Question

i m working on e commerce website . trying to keep apparel sizes as columns

snippet without "group by productid" clause

select d.id,
(case when s.sizeid = 1 then 1 else 0 end) as "S"  ,
(case when s.sizeid = 2 then 1 else 0 end) as "M",
(case when s.sizeid = 3 then 1 else 0 end) as "L"  ,
(case when s.sizeid = 4 then 1 else 0 end) as "XL",
(case when s.sizeid = 5 then 1 else 0 end) as "XXL"  
from datatest d
inner join stock s on d.id = s.productid

with "group by product id" results like this

select d.id,
(case when s.sizeid = 1 then 1 else 0 end) as "S"  ,
(case when s.sizeid = 2 then 1 else 0 end) as "M",
(case when s.sizeid = 3 then 1 else 0 end) as "L"  ,
(case when s.sizeid = 4 then 1 else 0 end) as "XL",
(case when s.sizeid = 5 then 1 else 0 end) as "XXL"  
from datatest d
inner join stock s on d.id = s.productid
group by d.id

please help me out

Answer 1

I guess what you want is the sum of all columns for sizes, sharing the same product id.

The SUM() function will do that for you when using GROUP BY

select d.id,
SUM( (case when s.sizeid = 1 then 1 else 0 end) ) as "S"  ,
SUM( (case when s.sizeid = 2 then 1 else 0 end) ) as "M",
SUM( (case when s.sizeid = 3 then 1 else 0 end) ) as "L"  ,
SUM( (case when s.sizeid = 4 then 1 else 0 end) ) as "XL",
SUM( (case when s.sizeid = 5 then 1 else 0 end) ) as "XXL"  
from datatest d
inner join stock s on d.id = s.productid
group by d.id