Pivot query Percentage and Price column showing

Question

Hi,

create table #temp1 (Slno INT,Name_ varchar(50))
insert into #temp1 (Slno,Name_) values (1,'LowPrice'),(2,'MediumPrice'),(1,'HighPrice')

create table #temp2 (Code Varchar(100),PriceType varchar(50),SalesPercent decimal(18,2),SalesPrice decimal(18,2))
insert into #temp2 (Code,PriceType,SalesPercent,SalesPrice)
values
('Code1','LowPrice',0,25), ('Code1','MediumPrice',0,30)
,('Code2','LowPrice',0,12), ('Code2','MediumPrice',0,18),('Code2','MediumPrice',0,20)
, ('Code3','HighPrice',5,0)

select * from #temp1
select * from #temp2

drop table #temp1,#temp2

i need output like below image

pricetype load dynamically

Answer 1

Check:

declare @s varchar(max) =   
    'select Code, ' +  
    stuff( (select ', ' +   
        'coalesce(sum(case PriceType when ' + quotename(Name_, '''') + ' then SalesPercent end), 0) ' + quotename(Name_ + '_Percent') + ',  
        coalesce(sum(case PriceType when ' + quotename(Name_, '''') + ' then SalesPrice end), 0) ' + quotename(Name_ + '_Price')  
        from #temp1  
        order by Slno  
        for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '') +  
    'from #temp2  
    group by Code  
    order by Code'  
  
execute (@s)  

You can use a simpler STRING_AGG in modern SQL Server.

Answer 2

Hi @Mohamed Farook
You could use static PIOVT like this:

--modified sample data  
create table #temp1 (Slno INT,Name_ varchar(50))  
insert into #temp1 (Slno,Name_) values (1,'LowPrice'),(2,'MediumPrice'),(3,'HighPrice')   
create table #temp2 (Code Varchar(100),PriceType varchar(50),SalesPercent decimal(18,2),SalesPrice decimal(18,2))  
insert into #temp2 (Code,PriceType,SalesPercent,SalesPrice)  
values  
('Code1','LowPrice',0,25), ('Code1','MediumPrice',0,30)  
,('Code2','LowPrice',0,12), ('Code2','MediumPrice',0,18),('Code2','HighPrice',0,20)  
, ('Code3','HighPrice',5,0)  

;WITH CTE AS  
(  
 SELECT Code,C.Column_Name,C.Column_Value  
 FROM #temp2 CROSS APPLY(VALUES(PriceType+'_Per',SalesPercent),(PriceType+'_Price',SalesPrice))C(Column_Name,Column_Value)  
)  
SELECT * FROM CTE  
PIVOT(SUM(Column_Value) FOR Column_Name IN([LowPrice_Per],[LowPrice_Price],[MediumPrice_Per],[MediumPrice_Price],[HighPrice_Per],[HighPrice_Price]))P  

Or dynamic PIVOT query like this:

DECLARE @sql_str VARCHAR(8000)  
DECLARE @spread_elements VARCHAR(8000)  
  
SELECT @spread_elements = ISNULL(@spread_elements + ',','') + QUOTENAME(Name_+'_Per')+ ',' + QUOTENAME(Name_+'_Price')    
FROM #temp1 ORDER BY Slno  
  
SET @sql_str = '  
;WITH CTE AS  
(  
 SELECT Code,C.Column_Name,C.Column_Value  
 FROM #temp2 CROSS APPLY(VALUES(PriceType+''_Per'',SalesPercent),(PriceType+''_Price'',SalesPrice))C(Column_Name,Column_Value)  
)  
SELECT * FROM CTE  
PIVOT(SUM(Column_Value) FOR Column_Name IN('+@spread_elements+'))P  
'  
EXEC (@sql_str)  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.