将行转换为列 SQL Server

Question

我希望将这个表格转换成更易懂的形式：

表格

我的目标是使用一些 SQL 命令将该表格转换为以下这种形式：

结果

我尝试了很多方法，但是我找不到正确的指示来完成它。

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

这不是正确的陈述。

我在这里有表格的脚本：

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');

- Henry Caballero

---

为什么你现在的方法不起作用呢？PIVOT 或者条件聚合（很多人更喜欢这种方式）是解决问题的正确工具。 - Thom A

Answer 1

使用条件聚合：

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

- GMB