Click here to Skip to main content
15,666,844 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 +'
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 = c.Data
where = c.Data
) as SourceTable
for city 
in ('+@ColumnNames +')) as PivotTable
Updated 16-Jul-18 22:23pm
MadMyche 16-Jul-18 13:12pm    
What are you using to present this? Or is the end-user using an SSMS type program
Member 13863605 16-Jul-18 13:15pm    
I am using ASP.NET

"Pre-screen" you data before pivotting; something like this:

... WHERE CHARINDEX( city, @ColumnNames ) > 0
Share this answer
Member 13863605 16-Jul-18 17:21pm    
doesn't work
Gerry Schmitz 16-Jul-18 17:59pm    
Since you didn't show your "new" SQL (that doesn't work), I'll assume you're just spouting off.

Or am I supposed to ask you to "please show us you new SQL" now.

Please. Huh, will ya?
@ColumnNames is for your pivot.

Have a separate variable for the list that the user entered e.g. @UserColumns and change your dynamic sql like this
set@SQL= N'select Time,'+@UserColumns +'
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900