Каждый разработчик 1С рано или поздно сталкивается с классической задачей: в базе данных некоторая информация хранится в виде плоских строк (например, связь «один ко многим», где у одного товара есть несколько строк описаний, характеристик или свойств), а пользователю или алгоритму требуется представить эти данные «горизонтально». Это означает, что несколько строк детальных записей по одному объекту необходимо либо объединить в одну текстовую строку через запятую, либо развернуть в отдельные именованные колонки таблицы (операция транспонирования или PIVOT).
В этой статье мы подробно разберем, почему стандартный язык запросов 1С имеет ограничения на подобные операции, и проанализируем четыре наиболее эффективных практических способа решения этой задачи. Мы изучим как методы чистого SQL-проектирования в запросах, так и элегантные возможности системы компоновки данных (СКД), а также самый производительный способ постобработки данных на встроенном языке 1С.
Представим, что в результате запроса мы получаем следующую плоскую выборку данных:
Наша цель — сгруппировать данные по товару и преобразовать их к одному из следующих видов:
---
Начнем с самого простого, надежного и наиболее производительного способа, который рекомендуется для использования в реальных промышленных задачах, когда объем данных достаточно велик. Попытки решить эту задачу исключительно средствами СУБД часто приводят к нечитаемому коду запроса и перегрузке сервера.
Для реализации этого метода мы должны гарантировать, что результат нашего запроса строго упорядочен по группировочному полю (в нашем случае — по полю Товар). Это позволит нам обойти выборку линейно за один проход, не используя неэффективный поиск в таблице значений.
Выясним причину высокой скорости этого алгоритма: вместо медленной конкатенации строк через оператор «+» в цикле, мы будем накапливать элементы в массив, а затем преобразовывать их в итоговую строку с помощью быстрой системной функции СтрСоединить(). Рассмотрим пример реализации:
// 1. Формируем исходный плоский запрос
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ТаблицаТоваров.Товар КАК Товар,
| ТаблицаТоваров.Описание КАК Описание
|ИЗ
| Справочник.ТоварыОписания КАК ТаблицаТоваров
|
|УПОРЯДОЧИТЬ ПО
| Товар"; // Сортировка КРИТИЧЕСКИ важна!
РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();
// 2. Создаем результирующую Таблицу Значений
ТаблицаРезультат = Новый ТаблицаЗначений;
ТаблицаРезультат.Колонки.Добавить("Товар", Новый ОписаниеТипов("СправочникСсылка.Товары"));
ТаблицаРезультат.Колонки.Добавить("ВсеОписания", Новый ОписаниеТипов("Строка"));
ТекущийТовар = Неопределено;
МассивОписаний = Новый Массив;
// 3. Линейный обход выборки
Пока Выборка.Следующий() Цикл
Если ТекущийТовар <> Выборка.Товар Тогда
// Если это не первая итерация и товар изменился — «пакуем» предыдущий товар
Если ТекущийТовар <> Неопределено Тогда
НоваяСтрока = ТаблицаРезультат.Добавить();
НоваяСтрока.Товар = ТекущийТовар;
НоваяСтрока.ВсеОписания = СтрСоединить(МассивОписаний, ", ");
КонецЕсли;
// Переходим к новому товару и очищаем массив накопления
ТекущийТовар = Выборка.Товар;
МассивОписаний.Очистить();
КонецЕсли;
// Добавляем описание текущей строки в массив
МассивОписаний.Добавить(Выборка.Описание);
КонецЦикла;
// 4. Не забываем обработать самую последнюю группу данных
Если ТекущийТовар <> Неопределено Тогда
НоваяСтрока = ТаблицаРезультат.Добавить();
НоваяСтрока.Товар = ТекущийТовар;
НоваяСтрока.ВсеОписания = СтрСоединить(МассивОписаний, ", ");
КонецЕсли;
Этот подход работает за линейное время O(N), не нагружает СУБД лишними соединениями и чрезвычайно прост в отладке.
---
Система компоновки данных (СКД) в 1С — это не просто инструмент для визуализации отчетов, а мощнейший процессор данных. Начиная с платформы 8.2.14, в СКД появилась встроенная функция агрегации СоединитьСтроки(). Она идеально решает задачу горизонтальной группировки на уровне платформы.
Вопреки распространенному заблуждению, результат работы СКД с транспонированными данными можно легко выгрузить в обычную коллекцию 1С (например, в ТаблицаЗначений), вообще не выводя отчет пользователю на экран.
Разберем по шагам, как настроить и программно выполнить такую схему:
Товар и Описание.Описание в качестве ресурса. В колонке "Выражение" укажите: СоединитьСтроки(ВычислитьВыражениеТаблица("Описание"), ", ") или просто СоединитьСтроки(Описание, ", ").Товар. В выбранные поля добавьте Товар и ресурс Описание.
// Программный вывод СКД в Таблицу Значений
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, СхемаКомпоновкиДанных.НастройкиПоУмолчанию,,, Тип("ГенераторМакетаКомпоновкиДанныхДляКоллекцииЗначений"));
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки);
ТаблицаРезультат = Новый ТаблицаЗначений;
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВКоллекциюЗначений;
ПроцессорВывода.УстановитьОбъект(ТаблицаРезультат);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
// В переменной ТаблицаРезультат теперь лежит свернутая таблица с объединенными строками!
Преимущества: Логика агрегации полностью переносится на сторону платформы. Решение отлично масштабируется и не требует написания сложных циклов разбора выборки.
---
Если вам требуется развернуть строки в колонки непосредственно внутри одного запроса (Вариант Б) и при этом вы заранее знаете точное количество или максимальный предел возможных описаний (например, вы точно знаете, что описаний у товара всегда не больше трех), можно применить классическую конструкцию агрегации с условным выбором.
Для этого в таблице обязательно должен присутствовать индекс или уникальный идентификатор строки внутри группы (например, числовой номер описания от 1 до 3). Разберем этот пример:
ВЫБРАТЬ
МТ.Товар КАК Товар,
МАКСИМУМ(ВЫБОР КОГДА МТ.НомерОписания = 1 ТОГДА МТ.Описание ИНАЧЕ "" КОНЕЦ) КАК Описание1,
МАКСИМУМ(ВЫБОР КОГДА МТ.НомерОписания = 2 ТОГДА МТ.Описание ИНАЧЕ "" КОНЕЦ) КАК Описание2,
МАКСИМУМ(ВЫБОР КОГДА МТ.НомерОписания = 3 ТОГДА МТ.Описание ИНАЧЕ "" КОНЕЦ) КАК Описание3
ИЗ
МояТаблица КАК МТ
СГРУППИРОВАТЬ ПО
МТ.Товар
Как это работает: Конструкция ВЫБОР КОГДА оставляет значение описания только для строго определенного индекса строки. Для всех остальных строк выводится пустая строка. Последующая группировка с функцией МАКСИМУМ «схлопывает» пустые значения, оставляя единственное заполненное описание в нужной колонке.
Если количество описаний заранее неизвестно, данный запрос можно формировать динамически на встроенном языке. Сначала легким вспомогательным запросом мы получаем количество и список индексов характеристик, а затем в цикле программно собираем текст основного запроса, конструируя строки МАКСИМУМ(ВЫБОР КОГДА...).
---
Если номера строки или индекса нет, но количество записей фиксировано и мало (например, ровно 3 строки), можно пойти путем явного соединения таблиц по равенству ключевого поля. Это решение упомянуто в сообщениях форума как альтернативный вариант без использования условного агрегирования:
ВЫБРАТЬ
вт.Товар,
вт.Описание КАК Описание1,
вт1.Описание КАК Описание2,
вт2.Описание КАК Описание3
ИЗ
вт КАК вт
ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт КАК вт1 ПО вт.Товар = вт1.Товар И вт.Описание <> вт1.Описание
ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт КАК вт2 ПО вт.Товар = вт2.Товар И вт.Описание <> вт2.Описание И вт1.Описание <> вт2.Описание
Важное предупреждение: Использование множественных внутренних или левых соединений одной и той же таблицы «сама на себя» по неоптимизированным условиям приводит к лавинообразному росту нагрузки на СУБД (происходит декартово произведение строк внутри группы). Использовать данный метод крайне не рекомендуется на реальных базах данных объемом более 1000 строк.
---
В Сообщении 2 на форуме упоминаются «темы ildarovich». В сообществе разработчиков 1С под этим термином понимаются сложные математические алгоритмы двоичного сворачивания строк непосредственно в SQL-запросах. Суть метода заключается в нумерации строк через соединение «больше-или-равно» и последующем каскадном сложении строк по степеням двойки.
Несмотря на изящество этого академического подхода, на практике он не нашел широкого применения, так как:
---
Для того чтобы выбрать наиболее подходящий инструмент, проанализируем сценарии использования: