Аналог PIVOT в запросе 1С (как выполнить транспонирование таблицы в запросе 1С)

06.08.21

Разработка - Запросы

+76 –
В статье показывается простой метод реализации аналога оператора PIVOT в запросе 1С без использования соединений.

Достаточно часто возникает задача транспонирования какой-нибудь таблицы, то есть превращения конкретных строк таблицы в отдельные столбцы. Рассмотрим как решается эта задача на простом примере.

Допустим, в справочнике номенклатуры у нас есть табличная часть "Справочник.Номенклатура.Свойства", в которой собраны свойства и их значения, ниже представлено её содержимое:

 

Ссылка Свойство Значение
Монитор BENQ GW2480 23.8" Размер диагонали (дюймы) 23,8
Монитор BENQ GW2480 23.8" Разрешение 1920x1080
Монитор BENQ GW2480 23.8" Тип матрицы IPS
Монитор IIYAMA ProLite XB2483HSU-B3 23.8" Размер диагонали (дюймы) 23,8
Монитор IIYAMA ProLite XB2483HSU-B3 23.8" Разрешение 1920x1080
Монитор IIYAMA ProLite XB2483HSU-B3 23.8" Тип матрицы VA
Монитор SAMSUNG U28E590D "R", 28" Размер диагонали (дюймы) 28,0
Монитор SAMSUNG U28E590D "R", 28" Разрешение 3840x2160
Монитор SAMSUNG U28E590D "R", 28" Тип матрицы TN

 

Нужно каждое свойство определённого вида записать в собственную отдельную колонку, то есть получить таблицу вот такого вида:

 

Ссылка РазмерДиагонали Разрешение ТипМатрицы
Монитор BENQ GW2480 23.8" 23,8 1920x1080 IPS
Монитор IIYAMA ProLite XB2483HSU-B3 23.8" 23,8 1920x1080 VA
Монитор SAMSUNG U28E590D "R", 28" 28,0 3840x2160 TN

 

Этого можно достигнуть при помощи комбинации операции группировки, агрегатной функции "МАКСИМУМ" и  оператора "ВЫБОР". Ниже приведён пример запроса, который выполняет транспонирование нашей исходной таблицы "Справочник.Номенклатура.Свойства":

 

ВЫБРАТЬ
	НоменклатураСвойства.Ссылка КАК Ссылка,
	
	МАКСИМУМ(
			ВЫБОР
				КОГДА НоменклатураСвойства.Свойство = &РазмерДиагонали
					ТОГДА НоменклатураСвойства.Значение
				ИНАЧЕ NULL
			КОНЕЦ
		) КАК РазмерДиагонали,

	МАКСИМУМ(
			ВЫБОР
				КОГДА НоменклатураСвойства.Свойство = &Разрешение
					ТОГДА НоменклатураСвойства.Значение
				ИНАЧЕ NULL
			КОНЕЦ
		) КАК Разрешение,

	МАКСИМУМ(
			ВЫБОР
				КОГДА НоменклатураСвойства.Свойство = &ТипМатрицы
					ТОГДА НоменклатураСвойства.Значение
				ИНАЧЕ NULL
			КОНЕЦ
		) КАК ТипМатрицы

ИЗ	Справочник.Номенклатура.Свойства КАК НоменклатураСвойства
ГДЕ	
	НоменклатураСвойства.Свойство В
	(
		&РазмерДиагонали,
		&Разрешение,
		&ТипМатрицы
	)

СГРУППИРОВАТЬ ПО
	НоменклатураСвойства.Ссылка

 

PIVOT Транспонирование

+76 –

См. также

Infostart Toolkit — Инструменты разработчика 1С 8.3

Инструментарий разработчика Роли и права Запросы СКД Программист Руководитель проекта Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Платные (руб)

Инструменты для разработчиков 1С 8.3: Infostart Toolkit. Автоматизация и ускорение разработки на управляемых формах. Легкость работы с 1С.

12000 руб.

02.09.2020    169355    937    403    

905
Шпаргалка по запросам

Запросы Программист Бесплатно (free)

Увидел cheatsheet по SQL и захотелось нарисовать подобное, но про запросы.

18.10.2024    11408    sergey279    18    

65
Ловушка функции запроса АВТОНОМЕРЗАПИСИ()

Запросы Программист Платформа 1С v8.3 Запросы Конфигурации 1cv8 Бесплатно (free)

Столкнулся с интересной ситуацией, которую хотел бы разобрать, ввиду её неочевидности. Речь пойдёт про использование функции запроса АВТОНОМЕРЗАПИСИ() и проблемы, которые могут возникнуть.

11.10.2024    6349    XilDen    36    

83
Внутренняя жизнь ваших запросов PostgreSQL. Как и зачем «подглядывать» в подробности

Запросы Программист Запросы Бесплатно (free)

Отлаживая взаимодействие с базой данных, мы регулярно сталкиваемся с зависающими или подозрительно долго выполняющимися обращениями, негативно влияющими на производительность. О том, как в PostgreSQL выявить подозрительные запросы, основываясь на доступной о них информации, расскажем в статье.

16.08.2024    9080    user1840182    5    

28
В помощь разработчику: преобразование табличного документа в запрос

Инструментарий разработчика Универсальные функции Запросы Программист Платформа 1С v8.3 Конфигурации 1cv8 1С:Бухгалтерия 3.0 1С:Зарплата и Управление Персоналом 3.x Бесплатно (free)

Внешняя обработка для создания запроса на основании табличных данных.

22.07.2024    6648    193    ixijixi    10    

38
Поиск дублей по полям ключей шапки, табличных частей

Математика и алгоритмы Запросы Программист Платформа 1С v8.3 Запросы Бесплатно (free)

Рассмотрим быстрый алгоритм поиска дублей с использованием hash функции по набору полей шапки и табличных частей.

08.07.2024    2732    ivanov660    9    

22
Итоговый запрос в отчете СКД

Запросы СКД Программист Стажер Система компоновки данных Россия Бесплатно (free)

Часто при разработке отчетов в СКД возникает ситуация, когда не совсем понятно, почему отчет выводит не те данные, которые нужны, либо не выводит вовсе. Возникает потребность увидеть конечный запрос, который формирует СКД. Как это сделать, рассмотрим в этой статье.

15.05.2024    10226    implecs_team    6    

48
Пропорциональное распределение в запросе с использованием АвтоНомерЗаписи()

Запросы Программист Стажер Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Часто поступают задачи по произвольному распределению общих сумм. После распределения иногда пропадают копейки. Суть решения добавить АвтоНомерЗаписи() в ВТ распределения, и далее используя функции МАКСИМУМ или МИНИМУМ можем положить разницу копеек в первую или последнюю строку знаменателя распределения.

11.04.2024    3629    andrey_sag    10    

38
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. ixijixi 1975 12.12.20 23:35 Сейчас в теме
Интересно было бы увидеть универсальный пример транспонирования данных с заранее неизвестными колонками.
А так конечно это в СКД за минуту решается
Merkalov; OD_OR; abasovit; Irwin; Hatson; user1353054; Summer_13; +7 – Ответить
2. Eugen-S 411 12.12.20 23:48 Сейчас в теме
(1) Да будет Вам известно, что в T-SQL PIVOT не универсален и в нём так же указывается определённое число колонок.
Не всегда есть возможность использовать СКД. Например, когда нужно сделать транспонирование таблицы в запросе динамического списка, то СКД там не применишь.

А что касается универсального варианта, то только программной генерацией запроса. Сначала запросом получаем список всех свойств (будущих колонок транспонированной таблицы), программно формируем по ним запрос PIVOT, а затем выполняем его.
EvCher; =Kollega=; Восьмой; +3 – Ответить
3. МимохожийОднако 142 13.12.20 07:54 Сейчас в теме
(2) Пример программной генерации есть?
Darklight; +1 – Ответить
5. Eugen-S 411 13.12.20 09:37 Сейчас в теме
(3) По просьбам трудящихся, публикую пример программной генерации: 

Запрос = Новый Запрос();
Запрос.Текст =
"ВЫБРАТЬ
|	НоменклатураСвойства.Свойство КАК Свойство,
|	НоменклатураСвойства.Свойство.Наименование КАК НаименованиеСвойства
|
|ИЗ	Справочник.Номенклатура.Свойства КАК НоменклатураСвойства
|
|СГРУППИРОВАТЬ ПО
|	НоменклатураСвойства.Свойство
|";

Выборка = Запрос.Выполнить().Выбрать();

ТекстЗапроса =
"ВЫБРАТЬ
|	НоменклатураСвойства.Ссылка КАК Ссылка
|";	
ПодстрокаЗапросаГДЕ = "";

Пока Выборка.Следующий() Цикл
		
	НаименованиеСвойства = СтрЗаменить(СокрЛП(Выборка.НаименованиеСвойства)," ", "");
	НаименованиеСвойства = СтрЗаменить(НаименованиеСвойства,"(", "");
	НаименованиеСвойства = СтрЗаменить(НаименованиеСвойства,")", "");
	
ТекстЗапроса = ТекстЗапроса +
",
|МАКСИМУМ (
|			ВЫБОР
|				КОГДА НоменклатураСвойства.Свойство = &" +НаименованиеСвойства + "
|					ТОГДА НоменклатураСвойства.Значение
|				ИНАЧЕ NULL
|			КОНЕЦ
|		) КАК " + НаименованиеСвойства;
	
	ПодстрокаЗапросаГДЕ = ПодстрокаЗапросаГДЕ + ",&" + НаименованиеСвойства + Символы.ПС;
	
	Запрос.УстановитьПараметр(НаименованиеСвойства, Выборка.Свойство);
	
КонецЦикла;

ПодстрокаЗапросаГДЕ = Сред(ПодстрокаЗапросаГДЕ, 2);
	
ТекстЗапроса = ТекстЗапроса +
"
|ИЗ	Справочник.Номенклатура.Свойства КАК НоменклатураСвойства
|ГДЕ	
|	НоменклатураСвойства.Свойство В
|	(" + 
		ПодстрокаЗапросаГДЕ + "
|	)
|СГРУППИРОВАТЬ ПО
|	НоменклатураСвойства.Ссылка
|";
		
Запрос.Текст = ТекстЗапроса;
тзРезультат = Запрос.Выполнить().Выгрузить();
Показать
EvCher; green_forest; tdml; Deutschman; mrKHrom; abasovit; user1463853; Восьмой; Hatson; ubnkfl; daho; kote; DrAku1a; Darklight; Cmapnep; eeeio; asg.aleks; user1503726; +18 – Ответить
7. Darklight 33 15.12.20 17:32 Сейчас в теме
(5)Лучше было бы обернуть это всё в универсальную функцию - для получения текста запроса - в идеале 4-х отдельных секций текстов (выборка, источники, условие, группировка), а ну да - условий тут нет (хотя для универсальности не помешали бы) - тогда 3 секции. Но это уже так - просто пожелания от себя. Как минимум - это должно быть оформлено функция генерации текста запроса - вот тогда статья будет боле менее закончена. Для полноты можно только добавить вариант транспонирования для переданной таблицы значения - в виде параметра (когда нужно передать её во временную таблицу одновременно транспонировав)

Отдельной функцией можно было бы рассмотреть и динамическую - которая сама определяет колонки по значению таблицы данных из СУБД источника - получает фактические колонки и строит уже запрос на выборку
+ –1 Ответить
12. Yashazz 4801 12.08.21 20:26 Сейчас в теме
(7) Я такие задачи, если честно, решаю программной генерацией, и всем советую. Оно как-то прозрачнее, атомарнее и управляемее выходит. Удобнее для разработок и переделок.

Но только не ту жуть, как в (5), ибо это ппц, а нормально, по нужной коллекции (в обсуждаемом случае - по свойствам), в цикле, набрать текст запроса и айда вперёд. Ну и не NULL, конечно, лепить, а нормальное типизированное пустое значение. Если ПВХ, то это совсем просто, если реквизиты - ну, передавать пустышки нужных типов в параметры.

А, вот, ncerber это примерно и предложил.
Irwin; Восьмой; +2 – Ответить
10. webester 26 16.12.20 05:14 Сейчас в теме
(5)Тогда мы получим список всех колонок, даже если они будут пустые. Если в наименовании свойства будут пробелы, это будет нерабочий запрос.
+ –1 Ответить
20. Yashazz 4801 13.08.21 09:26 Сейчас в теме
(2) К сожалению, в доступных нам инструментах языка запросов 1С отсутствует работа с коллекцией колонок таблицы. Мы не можем даже получить их имена и количество. Поэтому говорить об универсальности исключительно в "чистом" запросе - бесполезно. Так что да, только программной генерацией.
+ – Ответить
4. ncerber 2 13.12.20 08:24 Сейчас в теме
Если схематично то так 
Для Каждого Свойство Из СписокСвойств Цикл
   ТекстЗапроса = ТекстЗапроса + "
|,ВЫБОР 
|  КОГДА Свойство =&Свойство ТОГДА Свойство
|  ИНАЧЕ null Конец КАК &ИмяСвойства";
Запрос.УстановитПараметр("Свойство", Свойств.Значение);
Запрос.УстановитПараметр("ИмяСвойства", Свойство.Представление);
КонецЦикла
Запрос.Текст=ТекстЗапроса;
Показать

Здесь СписокСвойств это список значений. А запятая в начале ставится, чтобы не задумываясь о том, где и сколько их нужно ставить.
abasovit; Восьмой; creatermc; Yashazz; DrAku1a; Cmapnep; artbear; +7 – Ответить
6. Darklight 33 15.12.20 17:25 Сейчас в теме
(4)Пропущен агрегат "МАКСИМУМ()"
(5)Тут он есть
+ – Ответить
8. rabid_otter 134 15.12.20 19:10 Сейчас в теме
ну статью можно было сократить до нескольких фраз.
формируем ручками - ищем максимум в запросе с фильтром по нужному свойству, либо в СКД.
+ – Ответить
9. webester 26 16.12.20 05:13 Сейчас в теме
(1)Вывод в таблицу вы имеете ввиду? Там у меня наблюдаются проблемы с производительностью в случае большого количества строк (несколько сотен). Время вывода отчета прямо пропорционально количеству строк и занимает десятки секунд(от 10 до 40 секунд в моем случае)
+ – Ответить
11. Yashazz 4801 12.08.21 20:22 Сейчас в теме
Извините, но это фигня, а не транспонирование. Вы хоть основы линейной алгебры почитайте, прежде чем термин использовать.

Настоящее транспонирование матрицы одним красивым запросом - это крайне нетривиальная задача, адекватного решения которой я пока не видел и сам не нашёл, хотя ищу уже года три.

Я вообще давно перестал понимать, почему за такой жуткий примитив столько плюсов накидывают... Уровень уже что, совсем "разжевать и в ротик положить", да?...
d4rkmesa; Расим; lunjio; +3 – Ответить
13. lunjio 67 12.08.21 21:22 Сейчас в теме
(11) Описан достаточно тривиальный способ решения, я думаю каждый программист должен такое уметь, для остального есть Ildarovich
Восьмой; JOJ73; Yashazz; +3 – Ответить
15. Eugen-S 411 12.08.21 21:45 Сейчас в теме
(13) Если способ тривиален, почему на Инфостарте - эта публикация, за всё существование "Инфостарта" появилась первой? А на действительно тривиальные вещи на Инфостарте, как правило, находится далеко не одна публикация.
user1835100; Восьмой; +2 – Ответить
16. lunjio 67 12.08.21 22:45 Сейчас в теме
(15)
Не знаю, но как минимум с начала своего программирования применяю такие способ, в основном в запросах со свойствами.
+ – Ответить
14. Eugen-S 411 12.08.21 21:41 Сейчас в теме
(11)
Yashazz, все люди разные. В мире разработчиков 1С, хватает тех, кто никогда не имел дела с реализацией SQL от Microsoft и слово Pivot им не известно.
Человеческая память ассоциативна и первая ассоциация, которая приходит в голову, когда с таблицей нужно сделать операцию, которая описана выше - это транспонирование.

Собственно, отдельного термина эта табличная операция и не имеет.
А транспонирование, как его определяет математика, в мире 1С, по большему счёту, и ненужно.

Термин "Транспонирование" был взят как русскоязычный аналог "Pivot".
Чтобы те, кто не знает слово "Pivot", но вспомнит что-то про транспонирование, вбив в поисковик запрос, могли найти эту публикацию.



Я вообще давно перестал понимать, почему за такой жуткий примитив столько плюсов накидывают.



На счёт примитива я не соглашусь.
Возьмите 10-к программистов, и дайте им эту задачку.
И я уверен, что большинство из них напишут запросы с "бородой" из ЛЕВЫХ СОЕДИНЕНИЙ.

Ответьте, пожалуйста, на один простой вопрос, Вы с какой целью здесь пишете и какую пользу несут окружающим Ваши сообщения?
ivnik; GV.; Михаська; Восьмой; +4 – Ответить
17. Yashazz 4801 13.08.21 07:29 Сейчас в теме
Термин "Транспонирование" был взят как русскоязычный аналог "Pivot".
Вот, на мой взгляд, неудачно был взят этот термин. Потому что сбивает с толку. Я, собственно, увидел заголовок, обрадовался - вот, мол, хоть кто-то решил эту задачу... А тут такая простая штука и никакого настоящего транспонирования. Конечно, я огорчился.

(14)
Возьмите 10-к программистов, и дайте им эту задачку.
И я уверен, что большинство из них напишут запросы с "бородой" из ЛЕВЫХ СОЕДИНЕНИЙ.
Значит, это не программисты, а, кхм, не будем говорить публично грубости, кто. Тупокодеры в лучшем случае.

(14)
Вы с какой целью здесь пишете и какую пользу несут окружающим Ваши сообщения
Во-первых, уточняю терминологию, это о пользе. Во-вторых, написал, что нормально было бы в цикле обходить рассматриваемые свойства; в третьих, ИС это публичный ресурс, где постинги могут и не иметь иной цели, кроме высказывания мнения автора.
+ –1 Ответить
18. Eugen-S 411 13.08.21 08:32 Сейчас в теме
(17)
Вот, на мой взгляд, неудачно был взят этот термин. Потому что сбивает с толку. Я, собственно, увидел заголовок, обрадовался - вот, мол, хоть кто-то решил эту задачу...


Заголовок публикации начинается "Аналог PIVOT в запросе 1С", неужели Вы не знали, что Pivot не выполняет транспонирования таблицы, таким образом, каким его определяет математика?
Ведь с ваших же слов - это жуткий примитив.

Теперь насчёт транспонирования, как его определяет математика, если бы оно было реально нужно в СУБД, наверное во всех реализациях SQL появились бы те или иные команды, выполняющие такое действие с таблицами. Но за несколько десятков лет, такие команды так и не появились. Значит такое транспонирование в реальных задачах, стоящих перед СУБД ненужно.

И напоследок, введите в Яндексе поисковый запрос "pivot транспонирование" и Вы обнаружите энное количество публикаций, где эти два слова идут рука об руку. Потому что превращение строк в столбцы, которое происходит в результате применения Pivot, у программистов ассоциируется с операцией "транспонирование", пусть даже и не происходит классического транспонирования, определённого математикой.

Программирование - это обособленная область прикладной науки, а значит термины в ней вполне могут иметь свою трактовку, отличающуюся от других научных дисциплин.
EvCher; Восьмой; +2 – Ответить
19. Yashazz 4801 13.08.21 09:23 Сейчас в теме
(18) Ага, ага. Только вот Википедия об этом, к примеру, ничегошеньки не знает. Знает только классическое определение. А ссылка на "энное количество публикаций", увы, неубедительна. В интернете, к примеру, бессчётно случаев пунктуационных, синтаксических и орфографических ошибок, однако претендовать на "новые правила" они не могут, и на правила русского языка не влияют, оставаясь всего лишь ошибками - по незнанию и нежеланию знать.

С малой нужностью транспонирования в СУБД не спорю, я и не утверждал, что это каждодневно и прямо позарез. Я сказал ровно то, что сказал: что а) термин неудачен, б) ищу решение классической задачи запросом.
+ –1 Ответить
24. Восьмой 90 29.12.21 00:28 Сейчас в теме
(14) Спасибо за LevelUP
+ – Ответить
25. nafisa 03.10.22 07:17 Сейчас в теме
(14)
Человеческая память ассоциативна и первая ассоциация, которая приходит в голову, когда с таблицей нужно сделать операцию, которая описана выше - это транспонирование.

Спасибо за статью от начинающего, именно так и искала.
EvCher; Eugen-S; +2 – Ответить
21. gybson 15.10.21 19:20 Сейчас в теме
Лучше через объединить, кмк 

ВЫБРАТЬ
	НоменклатураСвойства.Ссылка КАК Ссылка,
	НоменклатураСвойства.Значение как Колонка 1
        NULL как Колонка2
ИЗ	Справочник.Номенклатура.Свойства КАК НоменклатураСвойства
ГДЕ НоменклатураСвойства.Свойство = &РазмерДиагонали

Объединить

ВЫБРАТЬ
	НоменклатураСвойства.Ссылка КАК Ссылка,
        NULL,
        НоменклатураСвойства.Значение
ИЗ	Справочник.Номенклатура.Свойства КАК НоменклатураСвойства
ГДЕ НоменклатураСвойства.Свойство = &Разрешение
Показать


Такой запрос и программно проще собрать
Восьмой; +1 – Ответить
22. МихаилМ 21.10.21 18:25 Сейчас в теме
(21) в мс скл можно нарваться на ошибку "Intra-query parallelism" https://its.1c.ru/db/metod8dev/content/5834/hdoc
Восьмой; +1 – Ответить
23. gybson 22.10.21 09:35 Сейчас в теме
(22)я сомневаюсь, что тут будут наложены хоть какие-то блокировки
Восьмой; +1 – Ответить
26. user1950534 27.03.24 15:57 Сейчас в теме
На SQL версиях до 2016 года примерно не было никакого PIVOT
Через аналогичные конструкции помню такие простыни выводили, ой мама не горюй))
+ – Ответить
27. gml 26.09.24 00:56 Сейчас в теме
А на postgres функция MAKCИМУМ что будет выбирать - null или непустую строку?
+ – Ответить
28. EvCher 26.09.24 22:27 Сейчас в теме
Сколько же нафлудили тут "умники". То термины не нравятся, то коды. А я вот нашел именно по "транспонированию" один из способов решения по своей задаче. Так что спасибо автору, который поделился решением. Мне кажется, только у нас за добрые дела надо оправдываться и еще убеждать кого-то. Ну не нравится вам - не пользуйте. А "рецензентов" хватает и без вас.
ivnik; Eugen-S; +2 – Ответить
Оставьте свое сообщение