Member 13863605 Ответов: 1

Использование динамического SQL для объединения двух столбцов в заголовок таблицы с соответствующими значениями

У меня есть два стола, список гостей и список клиентов. Я присоединился к ним и использовал динамическую сводную таблицу SQL для преобразования столбца "город" из таблицы GuestList в строки или заголовки таблиц, и среднее население будет отображаться под каждым городом. Таким образом, после выполнения запроса внизу заголовок моей таблицы выглядит следующим образом, и среднее население отображается под каждым городом.

Время| Атланта| Лос-Анджелес | Нью-Йорк | Денвер| Миннеаполис

Но я хочу, чтобы мой заголовок таблицы выглядел так. В основном "Id" имеет четыре значения: 1, 2,3,4, и каждый город имеет все эти четыре идентификатора. Я не мог бы добавить все города, но остальные города тоже будут такими.

Время| Атланта_1|| Атланта_2|| Атланта_3|| Атланта_4|

Не мог бы кто-нибудь помочь мне в этом, написав остальную часть запроса о том, как объединить два столбца в таблице GuestList и поместить под ним их соответствующее население.

Что я уже пробовал:

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'

1 Ответов

Рейтинг:
2

CHill60

У вас есть некоторые проблемы с этим SQL в его нынешнем виде ... избегайте использования зарезервированных слов в качестве имен столбцов, или если вы это сделаете, то вам следует окружить их [ ] - [Time] и [Population] в этом случае.

Этот фрагмент кода, который читается ...

inner join Split(@city, '','') as c
on a.city = c.Data
where a.city = c.Data
то WHERE клаузула просто повторяет то, что ON клаузула говорит, что это бессмысленно. Тем более что вы на самом деле ничего не используете из CustomerList Итак, JOIN само по себе это излишне.

Чтобы получить имена столбцов с идентификатором в качестве суффикса, вы можете сделать следующее (изменения подчеркнуты):
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 ...
Если вы подключите это (обратите внимание, что я использовал временные таблицы и избавился от лишних вещей)
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]'
к сожалению, вы получаете такие вещи....
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 и рассчитав максимальное количество этих столбцов...первый:
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 выглядит следующим образом (обратите внимание на order by переехал!)
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]'

Не очень красиво но кажется работает