Аналог 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 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

10000 руб.

02.09.2020    124819    682    389    

732

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

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

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

11.04.2024    2193    andrey_sag    10    

28

Для чего используют конструкцию запроса "ГДЕ ЛОЖЬ" в СКД на примере конфигурации 1С:ERP

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных 1С:ERP Управление предприятием 2 Бесплатно (free)

В типовых конфигурациях разработчики компании 1С иногда используют в отчетах, построенных на СКД, такую конструкцию, как "ГДЕ ЛОЖЬ". Такая конструкция говорит о том, что данные в запросе не будут получены совсем. Для чего же нужен тогда запрос?

13.02.2024    5993    KawaNoNeko    23    

25

Набор-объект для СКД по тексту или запросу

Запросы СКД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Есть список полей в виде текста, или запрос - закидываем в набор СКД.

1 стартмани

31.01.2024    2144    2    Yashazz    0    

30

Запрос 1С copilot

Инструментарий разработчика Запросы Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Пишем на человеческом языке, что нам надо, и получаем текст запроса на языке 1С. Используются большие языковые модели (LLM GPT) от OpenAI или Яндекс на выбор.

5 стартмани

15.01.2024    6624    31    mkalimulin    27    

51

PrintWizard: поддержка представлений ЗУП в конструкторе

Инструментарий разработчика Запросы Платформа 1С v8.3 Бесплатно (free)

Одной из интересных задач, стоящих в процессе разработки, была поддержка механизма представлений в ЗУП. Но не просто возможность исполнения запросов с ними. Основная проблема была в том, чтобы с ними было удобно работать, а именно: создавать, модифицировать и отлаживать. Кратко о том, что в итоге получилось...

14.12.2023    1878    vandalsvq    7    

29

Объектная модель запроса "Схема запроса" 2

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

Далеко уже не новый тип данных "Схема запроса". Статья о том, как использовать его "попроще". Примеры создания текста запроса с нуля и изменение имеющегося запроса.

06.12.2023    5624    user1923546    26    

46

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16589    skovpin_sa    14    

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

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

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

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

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

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

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

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

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

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

Здесь СписокСвойств это список значений. А запятая в начале ставится, чтобы не задумываясь о том, где и сколько их нужно ставить.
abasovit; Восьмой; creatermc; Yashazz; DrAku1a; Cmapnep; artbear; +7 –
6. Darklight 32 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 4722 12.08.21 20:22 Сейчас в теме
Извините, но это фигня, а не транспонирование. Вы хоть основы линейной алгебры почитайте, прежде чем термин использовать.

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

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

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

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



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



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

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

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

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


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

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

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

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

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

Спасибо за статью от начинающего, именно так и искала.
Eugen-S; +1 –
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
Через аналогичные конструкции помню такие простыни выводили, ой мама не горюй))
+ –
Оставьте свое сообщение