仅显示用户选择的列 [英] Display only the columns the user selects

问题描述

我有两个表，GuestList和CustomerList。我加入了他们并使用动态SQL数据透视表将来自GuestList表的'city'列转换为行或表标题，并且每个城市下都会显示平均人口，'Id'有三个值，1,2,3和每个城市拥有所有这三个ID。因此，在底部执行查询后，我的表头看起来像这样，每个城市下面都会显示平均人口数。



时间| Atlanta_1 | Atlanta_2 | Atlanta_3 | NY_1 | NY_2 | NY_3 | LA_1 | LA_2 | LA_3



我想只显示用户选择的列。因此，如果用户选择Atlanta_1，NY_2，LA_1，则只会显示这三列以及时间，而不会显示其他任何内容。我可以在我的存储过程中为用户要选择的列的字符串创建一个nvarchar参数，但是我需要在查询中进行哪些更改才能显示所选列而不是全部？请有人帮帮我。



我尝试过的事情：

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

推荐答案

在旋转之前预先筛选你的数据;像这样的东西：



... WHERE CHARINDEX（city，@ ColumnNames）> 0

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

... WHERE CHARINDEX( city, @ColumnNames ) > 0

@ColumnNames 适用于您的支点。



为用户输入的列表设置单独的变量，例如： @UserColumns 并更改动态sql，如

@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 +'
from 

这篇关于仅显示用户选择的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！