Dynamic pivot query

Question

Hi

I need dynamic pivot with sum of Amount and sum group of ID Balance value

create table #temp ( ID varchar(20),Type varchar(20),Amount Decimal(18,2),Balance Decimal(18,2))

INSERT INTO #temp (ID,Type,Amount,Balance)
VALUES
('1001','JOY',1000,250),
('1001','JOY',500,100),
('1002','JOY',200,10),
('1002','RAJ',3000,200),
('1002','KHAN',1000,600)

Amount column = ID sum of value

i need output like below image
pls help

Answer 1

Hi @Mohamed Farook ,

Welcome to Microsoft Q&A!

Please refer below and check whether it is working.

select ID  
,isnull([JOY],0) [JOY],  
 isnull([RAJ],0) [RAJ],  
 isnull([KHAN],0) [KHAN],  
 isnull(Balance,0) Amount from   
(  
select id,type  
,sum(Amount) over (partition by id, type order by id) Amount  
,sum(Balance) over (partition by id order by id ) Balance  
from #temp) s  
pivot   
(max(amount) for type in ([JOY],[KHAN],[RAJ])) p  

Below is the dynamic query.

declare @sql nvarchar(max)  
declare @s nvarchar(max)  
declare @s1 nvarchar(max)  
  
select @s=STUFF(( SELECT distinct  ',['+type+']'  FROM #temp FOR XML PATH('') ), 1, 1, '')  
select @s1=STUFF(( SELECT distinct  ',isnull(['+type+'],0) ['+type+']'  FROM #temp FOR XML PATH('') ), 1, 1, '')  
  
set @sql=N' select ID,'+@s1+',  
 isnull(Balance,0) Amount from   
(  
select id,type  
,sum(Amount) over (partition by id, type order by id) Amount  
,sum(Balance) over (partition by id order by id ) Balance  
from #temp) s  
pivot   
(max(amount) for type in ('+@s+')) p'  
   
EXECUTE sp_executesql  @sql  

Output:

ID	JOY	KHAN	RAJ	Amount  
1001	1500.00	0.00	0.00	350.00  
1002	200.00	1000.00	3000.00	810.00  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.

Answer 2

I have an introduction on how to write a dynamic pivot here: https://www.sommarskog.se/dynamic_sql.html#pivot

Bear in mind that dynamic SQL is an advanced feature, so if you have not worked with dynamic SQL before, you have a hill to climb.