I have a two tables, GuestList and CustomerList. I joined them and used dynamic SQL pivot table to convert the 'city' column from GuestList table into rows or table headers and the average population would be displayed under each city and 'Id' has three values, 1, 2,3 and each city has all these three ID's. So after executing the query at the bottom, my table header looks like this and the average population is displayed under each city.
Time| Atlanta_1| Atlanta_2| Atlanta_3| NY_1| NY_2| NY_3| LA_1| LA_2| LA_3
I want to display only the columns the user selects. So if the user selects Atlanta_1, NY_2, LA_1, only these three columns will be displayed along with the time, nothing else. I can create a nvarchar parameter in my stored procedure for the string of the columns the user is going to select but what changes would I need to make in my query just to display the selected columns instead of all? Could somebody help me with this please.
What I have tried:
declare @ColumnNames nvarchar(max) = ''
declare @SQL nvarchar(max) = ''
select @ColumnNames += QUOTENAME(a.address+'_'+Convert(Varchar(10),a.Id )) + ','
from GuestList as a
innerjoin CustomerList as b
on a.Id = b.Id
groupby a.address
orderby a.address
set@ColumnNames =left(@ColumnNames, LEN(@ColumnNames)-1)set@SQL= N'select Time,'+@ColumnNames +'
from
(
select a.Time, a.address+'_'+Convert(Varchar(10),a.Id ) as City, a.population, b.Gender
from GuestList as a
inner join CustomerList as b
on a.Id = b.Id
inner join Split(@city, '','') as c
on a.city = c.Data
where a.city = c.Data
) as SourceTable
pivot
(avg(population)
for city
in ('+@ColumnNames +')) as PivotTable