SQL Server Dynamic Pivot Sql Query display all from one table

oaxa6hgo 于 2023-03-07 发布在其他

关注(0)|答案(2)|浏览(148)

TABLE-A:-
| Custno | Name | Route | Phone |
| ------------ | ------------ | ------------ | ------------ |
| 1 | C1 | 1 | 12345 |
| 2 | C2 | 1 | 23456 |
| 3 | C3 | 2 | 34567 |
| 4 | C4 | 1 | 45678 |
| 5 | C5 | 1 | 56789 |

TABLE-B:-

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

Table-C

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

EXPECTED RESULT

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

What I tried Using 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-server

来源：https://stackoverflow.com/questions/67208774/dynamic-pivot-sql-query-display-all-from-one-table

2条答案

按热度按时间

vpfxa7rd1#

You can reconstruct the query

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

which contains LEFT JOIN rather than INNER JOIN , and STRING_AGG() function in order to generate the pivoted columns dynamically as in the following code block

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;

Demo

赞(0）回复(0）举报 2023-03-07

dgtucam12#

for those of us who SQL server < 2017 you can replace the @cols with the following

set @cols = (
select distinct stuff((
        select ',' + t1.[BrandName]
        from (select distinct [BrandName] from [Table-C]) t1
        order by t1.[BrandName]
        FOR XML PATH('')), 1, LEN(','), '') AS Brands
    )

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server Dynamic Pivot Sql Query display all from one table

2条答案

相关问题

热门标签

最新问答