Convert rows to columns SQL Server

user_14628299 0
Viewed 52

I am trying to convert this table to show it in more understandable way:

table

My goal is can convert that table into this using some SQL commands:

result

I have tried many things but I can't fine a correct instruction to do it:

select 
    Product, [dell], [hp], [2019], [2020]
from
    (select *
     from myTable a) src
pivot
    (sum(Sales)
        for Brand, years in ([dell], [hp], [2019], [2020])
    ) piv1

This is not a correct statement.

I have the script of the table here:

CREATE TABLE myTable 
(
    Id int IDENTITY(1,1) PRIMARY KEY,
    Product varchar(255),
    Brand varchar(255),
    years varchar(255),
    Sales decimal(18,2),
);

INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('mouse','dell','2019','900000');

INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('mouse','dell','2020','40000');

INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('mouse','hp','2019','80000');

INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('mouse','hp','2020','70000');

INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('monitor','dell','2019','500');

INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('monitor','dell','2020','400');

INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('monitor','hp','2019','700');

INSERT INTO myTable (Product, Brand, years, Sales)
VALUES ('monitor','hp','2020','600');
aggregate-functions pivot sum sql-server sql
1 Answers

Use conditional aggregation:

select product,
    sum(case when brand = 'dell' and years = 2019 then sales end) dell_2019,
    sum(case when brand = 'dell' and years = 2020 then sales end) dell_2020,
    sum(case when brand = 'hp'   and years = 2019 then sales end) hp_2019,
    sum(case when brand = 'hp'   and years = 2020 then sales end) hp_2020,
    sum(case when years = 2019 then sales end) total_2019,
    sum(case when years = 2020 then sales end) total_2020
from mytable
group by product
user_106767160
Related