I have a table data, "sales" that has the sales of each shop by product and by the shop.

I try to get the data in a format that give me the id of the item and the sales of each shop in the same row, something like this:

I try to left join the data with some (select * from sales where shop='01') as a but it doesn't work because the ON clause only joins with one table, in this case, a.

Here is the SQL query:

select * 
from 
    (select * 
     from sales 
     where shop = '01') as a 
left join
    (select * 
     from sales 
     where shop = '02') as b on a.id = b.id 
left join
    (select * 
     from sales 
     where shop = '03') as c on a.id = c.id

Following this logic, I lose the results 4, 10.0, 02 of the data, trying to change the ON clause like c.id = b.id join different the data and give me different results.

How can I solve this?

CodePudding user response：

Not really sure why you have a column for each shop value. Seems redundant when you have Sales1, Sales2 etc. But you can solve this a lot easier than all these queries by using conditional aggregation. Something like this should work for you.

select id
    , Sales1 = isnull(max(case when Shop = '01' then sales end), 0)
    , Shop1 = '01'
    , Sales2 = isnull(max(case when Shop = '02' then sales end), 0)
    , Shop2 = '02'
    , Sales3 = isnull(max(case when Shop = '03' then sales end), 0)
    , Shop3 = '03'
from sales
group by id