question

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 asked EchoLiu-msft commented

pivot table with dynamically increated column

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

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered EchoLiu-msft edited

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:
112374-image.png

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.



image.png (3.5 KiB)
image.png (3.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.