SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table mytable (Name varchar(20)
                      , Brand varchar(20)
                      , Sales int
                      , SalesDate date)
insert into mytable values('John', 'Nike', 13344, '2022-01-01')
1 rows affected

insert into mytable values('John', 'Adidas', 23453, '2022-01-01')
1 rows affected

insert into mytable values('John', 'Nike', 94343, '2022-01-01')
1 rows affected

insert into mytable values('John', 'Nike', 23456, '2022-01-02')
1 rows affected

insert into mytable values('John', 'Nike', 54678, '2022-01-03')
1 rows affected

insert into mytable values('John', 'Nike', 23643, '2022-01-03')
1 rows affected

insert into mytable values('John', 'Nike', 12310, '2022-01-04')
1 rows affected

insert into mytable values('John', 'Adidas', 43345, '2022-01-04')
1 rows affected

insert into mytable values('John', 'Adidas', 94575, '2022-01-05')
1 rows affected

insert into mytable values('John', 'Adidas', 23451, '2022-01-05')
1 rows affected

select row_number() over(order by SalesDate) as ID
       , max(Name)
       , SalesDate
       , max(case when Brand = 'Nike' then Sales else 0 end) MaxNikeSales
       , max(case when Brand = 'Adidas' then Sales else 0 end) MaxAdidasSales
       , max(case when Brand = 'Nike' then Sales else 0 end) - 
         max(case when Brand = 'Adidas' then Sales else 0 end) 
         as "SalesDifference (Nike - Adidas)"
from mytable
group by SalesDate


ID
(No column name)
SalesDate
MaxNikeSales
MaxAdidasSales
SalesDifference (Nike - Adidas)


1
John
2022-01-01
94343
23453
70890

2
John
2022-01-02
23456
0
23456

3
John
2022-01-03
54678
0
54678

4
John
2022-01-04
12310
43345
-31035

5
John
2022-01-05
0
94575
-94575

ID	(No column name)	SalesDate	MaxNikeSales	MaxAdidasSales	SalesDifference (Nike - Adidas)
1	John	2022-01-01	94343	23453	70890
2	John	2022-01-02	23456	0	23456
3	John	2022-01-03	54678	0	54678
4	John	2022-01-04	12310	43345	-31035
5	John	2022-01-05	0	94575	-94575