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