locked
Display only the columns the user selects RRS feed

  • Question

  • 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.

    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
    Monday, July 16, 2018 4:59 PM

Answers