sql - 将行转换为列 SQL Server

Question

我正在尝试转换此表格以更易于理解的方式显示它:

table

我的目标是可以使用一些 SQL 命令将该表转换成这样:

result

我已经尝试了很多东西，但我找不到正确的指令来做这件事:

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

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