使用动态SQL将两列连接到具有各自值的表头中 [英] Using dynamic SQL to concatenate two columns into a table header with their respective values

问题描述

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



时间|亚特兰大|洛杉矶|纽约|丹佛|明尼阿波利斯



但我希望我的表头看起来像这样。基本上'Id'有四个值，1,2,3,4，每个城市都有这四个ID。我无法添加所有城市，但其他城市也将如此。



时间| Atlanta_1 || Atlanta_2 || Atlanta_3 || Atlanta_4 |



有人可以通过编写剩下的查询来帮助我解决如何连接GuestList表中的两列并将它们各自的人口放在它下面的问题。



我的尝试：

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. 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| Los Angeles | New York | Denver| Minneapolis

But I want my table header to look like this. Basically 'Id' has four values, 1, 2,3,4 and each city has all these four ID's. I could not add all the cities but rest of the cities will also be like this.

Time| Atlanta_1|| Atlanta_2|| Atlanta_3|| Atlanta_4|

Could someone help me on this by writing the rest of the query on how to concatenate the two columns in the GuestList table and put their respective population underneath it.

What I have tried:

declare @ColumnNames nvarchar(max) = ''
declare @SQL nvarchar(max) = ''

select @ColumnNames += QUOTENAME(a.address) + ','
from GuestList as a
inner join CustomerList as b
on a.Id = b.Id
group by a.address
order by a.address 

set @ColumnNames = left(@ColumnNames, LEN(@ColumnNames)-1  )

set @SQL= N'
select Time,' + @ColumnNames + '
from 
(
select a.Time, a.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
order by Time'


execute sp_executesql @SQL,
                        N'@city nvarchar(max)'
                        ,@city = 'Atlanta,Los Angeles,New York'

推荐答案

这个SQL有一些问题，因为它代表...避免使用保留字作为列名，或者如果你这样做，你应该用[] - [时间] 和 [人口] 在这种情况下。



该代码读取的片段...

You have some issues with this SQL as it stands ... avoid using reserved words as column names, or if you do then you should surround them with [ ] - [Time] and [Population] in this case.

That code snippet that reads ...

inner join Split(@city, '','') as c
on a.city = c.Data
where a.city = c.Data

WHERE 子句只是重复什么 ON 条款是这样说的，这是毫无意义的。因为你实际上并没有使用 CustomerList 中的任何内容，所以 JOIN 本身就是多余的。



要使用ID作为后缀获取列名，可以执行以下操作（更改带下划线）：

the WHERE clause is just repeating what the ON clause is saying so it's pointless. Even more so as you are not actually using anything from CustomerList so the JOIN itself is redundant.

To get your column names with the ID as a suffix you can do the following (changes underlined):

select @ColumnNames += QUOTENAME(city + idcol) + ','
from #GuestList 
cross join (select '_' + cast(id as varchar) as idcol from #GuestList Group by Id) cx
group by city, idcol
order by city, idcol 

，它提供

[Atlanta_1],[Atlanta_2],[Atlanta_3],[Atlanta_4],[Denver_1],[Denver_2],[Denver_3],[Denver_4], etc ...

如果插入（注意我）已经使用了临时表并摆脱了多余的东西）

If you plug that in (note I've used temp tables and got rid of redundant stuff)

set @SQL= N'

select Time,' + @ColumnNames + '
from 
(
	select [Time], city, population, id, city + ''_'' + cast(Id as varchar) as cityWithId	from #GuestList
) as SourceTable
pivot
(avg(population) 
for cityWithId
in (' 
    + @ColumnNames + 
    ')) as PivotTable
group by [Time], ' + @ColumnNames + 
' order by [Time]'

你不幸得到这样的东西....

you unfortunately get stuff like this....

08:00:00	NULL	NULL	NULL	NULL	NULL	NULL	NULL	210
08:00:00	NULL	NULL	NULL	NULL	NULL	NULL	220	NULL
08:00:00	NULL	NULL	NULL	NULL	NULL	210	NULL	NULL
08:00:00	NULL	NULL	NULL	NULL	200	NULL	NULL	NULL
08:00:00	NULL	NULL	NULL	130	NULL	NULL	NULL	NULL
08:00:00	NULL	NULL	140	NULL	NULL	NULL	NULL	NULL
08:00:00	NULL	130	NULL	NULL	NULL	NULL	NULL	NULL
08:00:00	120	NULL	NULL	NULL	NULL	NULL	NULL	NULL

但您可以通过将整个事物包装在CTE中并计算这些列的MAX来处理...首先：

But you can handle that by wrapping the whole thing in a CTE and calculating the MAX of those columns...first:

declare @ColumnNames2 nvarchar(max) = ''
select @ColumnNames2 += 'MAX('+QUOTENAME(a.city + idcol) + ') as ' + QUOTENAME(a.city + idcol) +','
from #GuestList as a
cross join (select '_' + cast(id as varchar) as idcol from #GuestList Group by Id) cx
group by a.city, idcol
order by a.city 
set @ColumnNames2 = left(@ColumnNames2, LEN(@ColumnNames2)-1  )

将结果

MAX([Atlanta_1]) as [Atlanta_1],MAX([Atlanta_2]) as [Atlanta_2],MAX([Atlanta_3]) as [Atlanta_3],MAX([Atlanta_4]) as [Atlanta_4], etc...

然后动态sql构建看起来像这样（请注意的订单已移动！）

then the dynamic sql build looks like this (note the order by has moved!)

set @SQL= N'
;with CTE AS (
select Time,' + @ColumnNames + '
from 
(
	select [Time], city, population, id, city + ''_'' + cast(Id as varchar) as cityWithId
	from #GuestList
) as SourceTable
pivot
(avg(population) 
for cityWithId
in (' 
    + @ColumnNames + 
    ')) as PivotTable
group by [Time], ' + @ColumnNames + 
') select [Time], ' + @ColumnNames2 + ' from CTE GROUP BY [Time]
 order by [Time]'

不漂亮，但似乎有效

Not pretty but it seems to work

这篇关于使用动态SQL将两列连接到具有各自值的表头中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！