# Pivot query Percentage and Price column showing

161 Reputation points
2022-10-22T05:19:23.947+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,635 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,620 questions

1. 116.6K Reputation points
2022-10-22T07:57:01.083+00:00

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.

1. 26,621 Reputation points
2022-10-24T02:39:14.43+00:00

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)

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
'
EXEC (@sql_str)
``````

Best regards,
LiHong