Member 14495997 Ответов: 1

Но у меня опоры динамически на основе различных значений в source_column быть целевые столбцы

Привет,
мне нужно сделать динамический разворот по столбцу 1 различные значения в field_name изменяются на столбцы в target и field_value значения в столбце 2 становятся значениями в новых целевых столбцах

у меня есть my_source_table
row_id | foreign_key | Field_Name | field_value
1 | 1 | растение | цветок
2 | 1 | одежда | брюки
3 | 1 | Страна | США
4/2 | растение | дерево
5 | 2 | континент | Европа
6/2 | Страна | Франция
7 | 3 | растение | цветок
8 | 3 | страна | Бразилия
9 | 3 | транспортное средство | велосипед
10 | 3 | сезон | лето
11 | 1 | сезон | весна


НЕОБХОДИМЫЙ:
foreign_key | завод | одежда| страна |континент| транспортное средство | сезон
1 | цветок| брюки | США | нуль | нуль | весна
2 | дерево | нуль | Франция| Европа| нуль | нуль
3 | цветок| нуль | Бразилия| нуль | велосипед | лето

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

i have tried a number of pivots but 
(1), i do not know how what to do about the aggregate function as i have nothing to aggregate and
(2), i do not know how to determine the column headers dynamically, based on distinct values in the my_source_table.field_name column   


SELECT [foreign_key], [plant], [clothing], [country], [continent], [vehicle], [season]
FROM 
(SELECT *
FROM my_source_table) a
PIVOT
(
Sum(count(field_value)
FOR vehicle IN
( ([plant],[clothing],[country],[continent],[vehicle],[season] )
) AS pvt
ORDER BY foreign_key

CHill60

Я обычно использую MAX (), когда нечего агрегировать как таковое.
Кроме того - у меня есть пример динамического SQL в моей статье Циклы обработки в SQL Server[^]
А пока я постараюсь придумать вам конкретный ответ

1 Ответов

Рейтинг:
1

CHill60

Я разберу ваш вопрос по разделам ... во-первых

Цитата:
(1), я не знаю, что делать с агрегатной функцией, так как мне нечего агрегировать
Как я уже сказал в своем комментарии, если агрегировать нечего, то я обычно использую MAX() (или MIN()Это имеет чистый эффект просто возвращения значения, которое вы хотите. Вот вам пример:
SELECT [foreign_key], [plant], [clothing], [country], [continent], [vehicle], [season]
FROM 
(
	SELECT row_id, foreign_key, Field_Name, field_value
	FROM my_source_table
) a
PIVOT
(
	MAX(field_value)
		FOR Field_Name IN ([plant],[clothing],[country],[continent],[vehicle],[season] )
) AS pvt
ORDER BY foreign_key
Примечание в вашей попытке вы использовали FOR vehicle IN ... Колонка [транспортное средство] фактически не существует до тех пор, пока не будет задействована ось, поэтому вы не можете ссылаться на нее в теле оси. Вы также не хотите этого делать, вы преобразуете значения [Field_Name] в столбцы, так что это то, что должно быть включено в это предложение.

К сожалению, запрос в его нынешнем виде приводит к таким бесполезным результатам...
foreign_key	plant	clothing	country	continent	vehicle	season
1	flower	NULL	NULL	NULL	NULL	NULL
1	NULL	pants	NULL	NULL	NULL	NULL
1	NULL	NULL	usa	NULL	NULL	NULL
1	NULL	NULL	NULL	NULL	NULL	spring
2	tree	NULL	NULL	NULL	NULL	NULL
2	NULL	NULL	NULL	europe	NULL	NULL
2	NULL	NULL	france	NULL	NULL	NULL
3	flower	NULL	NULL	NULL	NULL	NULL
3	NULL	NULL	brazil	NULL	NULL	NULL
3	NULL	NULL	NULL	NULL	bike	NULL
3	NULL	NULL	NULL	NULL	NULL	summer
Нам нужно каким-то образом их объединить ... например 
SELECT [foreign_key], MAX([plant]) AS [plant], MAX([clothing]) as [clothing], MAX([country]) as [country], MAX([continent]) as [continent], MAX([vehicle]) as [vehicle], MAX([season]) as [season]
FROM 
(
	SELECT row_id, foreign_key, Field_Name, field_value
	FROM my_source_table
) a
PIVOT
(
	MAX(field_value)
		FOR Field_Name IN ([plant],[clothing],[country],[continent],[vehicle],[season] )
) AS pvt
GROUP BY foreign_key
ORDER BY foreign_key
Обратите внимание, что я делаю агрегат на наборе данных, созданном PIVOT, "а также" агрегат в pivot. Это приводит к вашим ожидаемым результатам
foreign_key	plant	clothing	country	continent	vehicle	season
1	flower	pants	usa	NULL	NULL	spring
2	tree	NULL	france	europe	NULL	NULL
3	flower	NULL	brazil	NULL	bike	summer

Цитата:
(2), я не знаю, как определить заголовки столбцов динамически, основываясь на различных значениях в таблице my_source_table.колонки имя_поля
Для этого вам нужно сгенерировать некоторый динамический SQL. Давайте посмотрим на первый бит, который может измениться в зависимости от данных...сначала давайте добавим некоторые дополнительные данные 
insert into my_source_table (foreign_key, Field_Name, field_value) values (1, 'sense', 'smell')
Таким образом, мы знаем, что нам придется "построить" что-то, что добавляет это новое значение к биту SQL, который в настоящее время говорит 
SELECT [foreign_key], [plant], [clothing], [country], [continent], [vehicle], [season]
Есть несколько способов построения этого списка - потому что я хочу добавить несколько квадратных скобок, это, вероятно, самый простой 
DECLARE @liststr NVARCHAR(MAX) = null
;WITH fieldValues AS
(
	SELECT DISTINCT Field_Name FROM my_source_table
)
SELECT @liststr = COALESCE(@liststr +'],[','') + Field_Name 
FROM fieldValues
SELECT '[' + @liststr + ']'
Это даст свои результаты 
[clothing],[continent],[country],[plant],[season],[sense],[vehicle]
Обратите внимание, что я не могу контролировать порядок, в котором они возвращаются, если только я не заказываю по запросу. Этот порядок должен быть вне CTE ... то есть. Я не могу этого сделать 
SELECT DISTINCT Field_Name FROM my_source_table ORDER BY Field_Name
Я должен использовать 
FROM fieldValues  ORDER BY Field_Name
Также обратите внимание, что переменная начинается как null, и я должен добавить недостающую скобку в конце. Дайте ему попробовать другой способ, чтобы понять, почему.

Теперь давайте обратимся к другому биту, который должен измениться теперь, когда мы добавили новые данные, - сказал исходный запрос 
SELECT [foreign_key], MAX([plant]) AS [plant], MAX([clothing]) as [clothing], MAX([country]) as [country], MAX([continent]) as [continent], MAX([vehicle]) as [vehicle], MAX([season]) as [season]
Это немного более неудобно, так как мне нужно дважды обратиться к имени Поля и включить функцию MAX. Хорошенько посмотрите на разницу в том, как я это делаю. Также обратите внимание, что я хочу игнорировать первые два символа, когда использую результаты
DECLARE @liststr2 NVARCHAR(MAX) = null
;WITH MAXfieldValues AS
(
	SELECT DISTINCT Field_Name, Field_Name AS FN FROM my_source_table
)
SELECT @liststr2 = COALESCE(@liststr2,'')  +'],MAX([' + Field_Name + ']) AS [' + Field_Name
FROM MAXfieldValues ORDER BY Field_Name
SELECT SUBSTRING(@liststr2,3, LEN(@liststr2)) + ']'
Это приводит к результатам 
MAX([clothing]) AS [clothing],MAX([continent]) AS [continent],MAX([country]) AS [country],MAX([plant]) AS [plant],MAX([season]) AS [season],MAX([sense]) AS [sense],MAX([vehicle]) AS [vehicle]
Мы уже знаем, как должна выглядеть остальная часть запроса, поэтому давайте сошьем все вместе и запустим его 
DECLARE @sql NVARCHAR(MAX) = N'SELECT [foreign_key], ' + SUBSTRING(@liststr2,3, LEN(@liststr2)) + ']' +
' FROM 
(
	SELECT row_id, foreign_key, Field_Name, field_value
	FROM my_source_table
) a
PIVOT
(
	MAX(field_value)
		FOR Field_Name IN ([' + @liststr + '] )) AS pvt
GROUP BY foreign_key
ORDER BY foreign_key'

Exec sp_executesql @sql
И результаты тоже... 
FK	clothing	continent	country	plant	season	sense    vehicle
1	pants	NULL	usa	flower	spring	smell	NULL
2	NULL	europe	france	tree	NULL	NULL	NULL
3	NULL	NULL	brazil	flower	summer	NULL	bike
Я действительно призываю вас проработать различные разделы, чтобы убедиться, что вы полностью понимаете, что происходит. Есть ссылки на документацию и другие примеры в статье ссылка в моем комментарии

Maciej Los

Отличная работа!