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)

;with T1 as
(
select B.*,F.Season,F.Count  from B
inner join F on B.Id = F.BId
)
SELECT Name,
       MAX(CASE WHEN Year = 109 THEN Status END),
       MAX(CASE WHEN Year = 110 THEN Status END),
       MAX(CASE WHEN Year = 109 AND Season = 1 THEN [COUNT] END),
       MAX(CASE WHEN Year = 109 AND Season = 2 THEN [COUNT] END),
       MAX(CASE WHEN Year = 109 AND Season = 3 THEN [COUNT] END),
       MAX(CASE WHEN Year = 110 AND Season = 1 THEN [COUNT] END),
       MAX(CASE WHEN Year = 110 AND Season = 2 THEN [COUNT] END),
       MAX(CASE WHEN Year = 110 AND Season = 3 THEN [COUNT] END)
FROM T1
GROUP BY Name

Name	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)
中油	null	4	null	null	null	null	4487	36998
海底撈	3	null	null	7789	null	null	null	null
王品	6	2	7777	9999	15972	77999	4889	5228

Warning: Null value is eliminated by an aggregate or other SET operation.