Dynamic Pivot Sql Query отображает все из одной таблицы

ТАБЛИЦА-А:-

Custno Name Route Phone
1 C1 1 12345
2 C2 1 23456
3 C3 2 34567
4 C4 1 45678
5 C5 1 56789

ТАБЛИЦА-Б:-

ODate Custno Route ProductId qty
2021-04-22 1 1 1 100
2021-04-22 1 1 3 200
2021-04-22 2 1 1 120

Таблица-C

ProductId BrandName
1 Brand-1
2 Brand-2
3 Brand-3

ОЖИДАЕМЫЙ РЕЗУЛЬТАТ

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

Что я пробовал с помощью Dynamic Pivot

DECLARE @query  AS VARCHAR(MAX)
, @cols_ AS  vARCHAR(MAX)

--Making the column list dynamically 
select @cols_ = STUFF((SELECT ',' + QUOTENAME(brandname) from [Table-C] order by productid FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')              


print @cols_
--preparing PIVOT query dynamically. 

SET @query  = ' SELECT
       pivoted.* 
      into #Temp_data
      FROM 
      (
    select a.phone,a.custno,a.[name],d.BrandName,c.qty from [Table-A] a inner join [Table-B] c on a.custno = c.custno inner join [Table-C] d on c.productid = d.Productid and a.Route='1' and c.odate='2021-04-22'

      ) AS [p]
      PIVOT
      (
         MIN([P].[qty]) 
         FOR  [P].[BrandName]  IN (' + @cols_ + ')
      ) AS pivoted

      order by custno;

      select *        
       from #Temp_data [B]
      -- GROUP BY [B].[ODate]  

      drop table #Temp_data
   ';
  EXEC (@query)

sql sql-server pivot dynamic-pivot
person Sathesh Kumar    schedule 22.04.2021    source источник
comment
Отвечает ли это на ваш вопрос? Динамический SQL T-SQL и временные таблицы   -  person Serg    schedule 22.04.2021
comment
Нет, это отличается от этого   -  person Sathesh Kumar    schedule 01.05.2021

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете реконструировать запрос

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

который содержит LEFT JOIN, а не INNER JOIN, и функцию STRING_AGG() для динамического создания сводных столбцов, как в следующем блоке кода.

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;

Демо

person Barbaros Özhan    schedule 22.04.2021