pivot table with dynamically increated column

Question

pivot table with dynamically increated column

kasim mohamed 581

Hi,
I have a table like below
create table #table1 (EmpName varchar(50), Section nvarchar(200));
create table #Result (EmpName varchar(50), Section1 nvarchar(200), Section2 nvarchar(200), Section3 nvarchar(200));

insert into #table1 values ('AAA', '1111')
insert into #table1 values ('AAA', '2222')
insert into #table1 values ('AAA', '3333')

insert into #table1 values ('BBB', '1111')
insert into #table1 values ('BBB', '2222')

insert into #table1 values ('CCC', '3333')

insert into #Result values ('AAA', '1111', '2222', '3333')
insert into #Result values ('BBB', '1111', '2222', '')
select * from #table1;
select * from #Result
drop table #table1;
drop table #Result;

i need the result as Result table. the column count change dynamically.

Thanks

EchoLiu-MSFT 14,626 Reputation points

2021-07-08T01:25:57.903+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.

2 answers

Your answer

EchoLiu-MSFT 14,626 Reputation points

2021-07-08T01:25:57.903+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.

Answer 1

Please check:

DECLARE @sql nvarchar(max)  
DECLARE @s nvarchar(max)  
  
SELECT @s=STUFF(( SELECT DISTINCT ',['+CAST(Section as nvarchar)+']'  
FROM #table1 FOR XML PATH('')  ), 1, 1, '')  
      
 SET @sql=N';WITH cte  
 as(SELECT EmpName,CAST(Section as nvarchar) Section  
 FROM #table1)  
   
 SELECT * FROM (SELECT EmpName,Section FROM cte) as t    
 pivot (MAX(Section) FOR Section in ('+@s+')) as p'  
      
 EXECUTE sp_executesql  @sql

Output：

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Erland Sommarskog 134.3K MVP Volunteer Moderator

I have a primer on how to implement a dynamic pivot here:
https://www.sommarskog.se/dynamic_sql.html#pivot.

0 comments

Share via

pivot table with dynamically increated column

2 answers

Your answer