Hi @SQL ,
Glad that you already received your expected answer.
Please also refer blow dynamic pivot way from below:
declare @query nvarchar(max);
declare @cols nvarchar(max);
declare @cols1 nvarchar(max);
select @cols = stuff((select ','+quotename(AddrType)
from #Test
group by AddrType
for xml path('')), 1, 1, '');
select @cols1 = stuff((select ','+quotename(AddrType)+' '+AddrType+'City'
from #Test
group by AddrType
for xml path('')), 1, 1, '');
set @query = 'select ID,'+@cols1+'
from #Test
pivot
(
max(HomeCity) for AddrType in (' +@cols+ ')
) p
order by ID'
exec sp_executesql @query;
Output:
ID HomeCity MailingCity
10 Phoenix Boston
20 Memphis Durham
30 NULL New York
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.