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-2 | 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)
2条答案
按热度按时间vpfxa7rd1#
You can reconstruct the query
which contains
LEFT JOIN
rather thanINNER JOIN
, andSTRING_AGG()
function in order to generate the pivoted columns dynamically as in the following code blockDemo
dgtucam12#
for those of us who SQL server < 2017 you can replace the @cols with the following