Using dynamic SQL to concatenate two columns into a table header with their respective values

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

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'

Posted 11-Jul-18 11:17am

Member 13863605

Updated 13-Jul-18 4:34am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2018-07-13T04:34:00

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

SQL

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

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):

SQL

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

which gives

[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)

SQL

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

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

SQL

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  )

which gives the results

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

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

SQL

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