SQL Server 2014

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

CREATE TABLE [dbo].[B] (
  [Id] char(10),
  [Name] nvarchar(20),
  [Year] char(10),
  [Status] varchar(20)
  );

INSERT INTO B 
  VALUES ('1',N'王品','109','6'),('2',N'王品','110','2'),
  ('3',N'中油','110','4'),('4',N'海底撈','109','3')
 
  
  
CREATE TABLE [dbo].[F] (
  [Id] char(10),
  [BId] char(10),
  [Season] char(10),
  [Count] varchar(20)
  );
  
  INSERT INTO F
  VALUES ('A','1','1','7777'), ('B','1','2','9999'), ('C','1','3','15972'),
  ('D','2','1','77999'), ('E','2','2','4889'), ('F','2','3','5228'),
   ('G','3','1',null), ('H','3','2','4487'), ('I','3','3','36998'),
    ('J','4','1',null), ('K','4','2','7789'), ('L','4','3',null)
   
  
SELECT  *FROM B
select * from F
select * from B
inner join F on B.Id = F.BId

;with T1 as
(
select B.*,F.Season,F.Count  from B
inner join F on B.Id = F.BId
)

select  distinct
Name,
(select top 1 Status from T1 as T2 where T1.Name = T2.Name and Year=109 ) as [109_Status],
(select top 1 Status from T1 as T2 where T1.Name = T2.Name and Year=110 ) as [110_Status],
(select top 1 Count from T1 as T2 where T1.Name = T2.Name and Year=109 and Season=1 ) as [109_s1_count],
(select top 1 Count from T1 as T2 where T1.Name = T2.Name and Year=109 and Season=2 ) as [109_s2_count],
(select top 1 Count from T1 as T2 where T1.Name = T2.Name and Year=109 and Season=3 ) as [109_s3_count],
(select top 1 Count from T1 as T2 where T1.Name = T2.Name and Year=110 and Season=1 ) as [110_s1_count],
(select top 1 Count from T1 as T2 where T1.Name = T2.Name and Year=110 and Season=2 ) as [110_s2_count],
(select top 1 Count from T1 as T2 where T1.Name = T2.Name and Year=110 and Season=3 ) as [110_s3_count]
from T1

Id	Name	Year	Status
1	王品	109	6
2	王品	110	2
3	中油	110	4
4	海底撈	109	3

Id	BId	Season	Count
A	1	1	7777
B	1	2	9999
C	1	3	15972
D	2	1	77999
E	2	2	4889
F	2	3	5228
G	3	1	null
H	3	2	4487
I	3	3	36998
J	4	1	null
K	4	2	7789
L	4	3	null

Id	Name	Year	Status	Id	BId	Season	Count
1	王品	109	6	A	1	1	7777
1	王品	109	6	B	1	2	9999
1	王品	109	6	C	1	3	15972
2	王品	110	2	D	2	1	77999
2	王品	110	2	E	2	2	4889
2	王品	110	2	F	2	3	5228
3	中油	110	4	G	3	1	null
3	中油	110	4	H	3	2	4487
3	中油	110	4	I	3	3	36998
4	海底撈	109	3	J	4	1	null
4	海底撈	109	3	K	4	2	7789
4	海底撈	109	3	L	4	3	null

Name	109_Status	110_Status	109_s1_count	109_s2_count	109_s3_count	110_s1_count	110_s2_count	110_s3_count
中油	null	4	null	null	null	null	4487	36998
海底撈	3	null	null	7789	null	null	null	null
王品	6	2	7777	9999	15972	77999	4889	5228