SQL Server 2019

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

CREATE TABLE [Table-A](Custno INT, Name VARCHAR(10), Route INT, Phone INT)
INSERT [Table-A] VALUES 
(1,'C1',1,12345),
(2,'C2',1,23456),
(3,'C3',2,34567),
(4,'C4',1,45678),
(5,'C5',1,56789)
5 rows affected

CREATE TABLE [Table-B](ODate DATE, Custno INT, Route INT, ProductId INT, qty INT)
INSERT [Table-B] VALUES 
('2021-04-22',1,1,1,100),
('2021-04-22',1,1,3,200),
('2021-04-22',2,1,1,120)
3 rows affected

CREATE TABLE [Table-C](ProductId INT, BrandName VARCHAR(10))
INSERT [Table-C] VALUES 
(1,'Brand-1'),
(2,'Brand-2'),
(3,'Brand-3')
3 rows affected

SELECT *  
  FROM
  (
     SELECT A.[Phone], A.[CustNo], A.[Name], C.[BrandName], B.[qty]
       FROM [Table-A] AS A 
       LEFT JOIN [Table-B] AS B
         ON A.[CustNo] = B.[CustNo] 
        AND B.[odate] = '2021-04-22'
       LEFT JOIN [Table-C] AS C on C.productid = B.Productid  
      WHERE A.[Route] = 1  
      ) t
    PIVOT 
    (
     MIN([qty]) FOR [BrandName] IN ([Brand-1],[Brand-2],[Brand-3])
    ) AS piv


Phone
CustNo
Name
Brand-1
Brand-2
Brand-3


12345
1
C1
100
null
200

23456
2
C2
120
null
null

45678
4
C4
null
null
null

56789
5
C5
null
null
null


DECLARE @cols  AS NVARCHAR(MAX),  @query AS NVARCHAR(MAX)

SET @cols = ( SELECT STRING_AGG(QUOTENAME([BrandName]),',') 
                FROM (SELECT DISTINCT [BrandName] 
                        FROM [Table-C] ) C );

SET @query = 
  N'SELECT *  
      FROM
      (
         SELECT A.[Phone], A.[CustNo], A.[Name], C.[BrandName], B.[qty]
           FROM [Table-A] AS A 
           LEFT JOIN [Table-B] AS B
             ON A.[CustNo] = B.[CustNo] 
            AND B.[odate] = ''2021-04-22''
           LEFT JOIN [Table-C] AS C on C.productid = B.Productid  
          WHERE A.[Route] = 1  
          ) t
        PIVOT 
        (
         MIN([qty]) FOR [BrandName] IN (' + @cols + N')
        ) AS piv'

EXEC sp_executesql @query;


Phone
CustNo
Name
Brand-1
Brand-2
Brand-3


12345
1
C1
100
null
200

23456
2
C2
120
null
null

45678
4
C4
null
null
null

56789
5
C5
null
null
null

Phone	CustNo	Name	Brand-1	Brand-2	Brand-3
12345	1	C1	100	null	200
23456	2	C2	120	null	null
45678	4	C4	null	null	null
56789	5	C5	null	null	null