Как развернуть результат запроса 1С по горизонтали (транспонировать строки в колонки)?

Рассмотрим распространенную задачу при разработке в 1С: как развернуть результаты выборки по горизонтали. Проанализируем ситуацию: у нас есть справочник участников, и у каждого участника может быть несколько детей с разными датами рождения. Данные хранятся в строках (один участник — много строк с датами), а нам необходимо получить результат, где участник выводится одной строкой, а даты рождения разложены по колонкам: Дата1, Дата2, Дата3 и так далее.

Сложность заключается в ограничениях платформы 1С. Во-первых, в языке запросов 1С отсутствуют оконные функции, такие как ROW_NUMBER(), которые позволили бы легко пронумеровать строки внутри группы. Во-вторых, количество и имена колонок в результате запроса должны быть жестко определены на этапе его компиляции, платформа не умеет генерировать динамическое число колонок "на лету" силами самого языка запросов.

Разберем основные и наиболее эффективные способы решения этой проблемы.

Решение 1: Использование Системы Компоновки Данных (СКД)

Если конечная цель — формирование отчета, то самым правильным подходом будет отказ от попыток развернуть данные в самом запросе. Универсальный отчет, построитель отчета и, в первую очередь, СКД отлично справляются с этой задачей.

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

Решение 2: Нумерация строк через самосоединение в запросе

Если транспонирование необходимо выполнить исключительно внутри большого пакета запросов, можно пронумеровать строки средствами самого языка запросов. Для этого применяется метод самосоединения таблицы.

Проанализируем алгоритм:

1. Мы соединяем временную таблицу с исходными данными саму с собой (ЛЕВОЕ или ВНУТРЕННЕЕ СОЕДИНЕНИЕ) по полю группировки (например, по ссылке на участника).
2. Добавляем условие связи, сравнивающее уникальное поле сортировки. Обычно это дата или внутренний идентификатор: Таблица1.ДатаРождения >= Таблица2.ДатаРождения.
3. Применяем агрегатную функцию КОЛИЧЕСТВО(). Она подсчитает, сколько записей во второй таблице меньше или равны текущей записи из первой таблицы, что по сути даст нам порядковый номер строки.
4. Получив номер, используем конструкцию ВЫБОР КОГДА... для распределения по колонкам:

МАКСИМУМ(ВЫБОР КОГДА НомерСтроки = 1 ТОГДА ДатаРождения ИНАЧЕ ДАТАВРЕМЯ(1,1,1) КОНЕЦ) КАК Дата1,
МАКСИМУМ(ВЫБОР КОГДА НомерСтроки = 2 ТОГДА ДатаРождения ИНАЧЕ ДАТАВРЕМЯ(1,1,1) КОНЕЦ) КАК Дата2

Важное предупреждение: Этот метод порождает декартово произведение строк внутри каждой группы. Для небольших выборок это работает отлично, но на больших объемах данных производительность запроса критически упадет.

Решение 3: Предварительная нумерация через Таблицу Значений

Если данных много и производительность критична, разделим задачу на этапы, используя возможности встроенного языка 1С:

1. Выполняем простой первый запрос, который извлекает только пары "Участник — Дата рождения", упорядоченные нужным образом (по участнику и дате).
2. Выгружаем результат запроса в Таблицу Значений.
3. Добавляем в эту таблицу новую колонку НомерСтроки (тип Число).
4. Обходим Таблицу Значений в цикле. Заводим переменную-счетчик. Если участник в текущей строке совпадает с участником в предыдущей, увеличиваем счетчик на 1. Если участник сменился — сбрасываем счетчик в 1. Записываем значение счетчика в колонку НомерСтроки.
5. Передаем заполненную Таблицу Значений обратно в следующий запрос в качестве параметра (через построитель или как параметр виртуальной таблицы) и используем конструкцию с МАКСИМУМ(ВЫБОР...) для раскладки по колонкам, как в предыдущем примере.

Этот метод работает значительно быстрее самосоединения на больших объемах данных.

Решение 4: Динамическая генерация текста запроса

Поскольку мы не знаем заранее максимальное количество дат (у кого-то один ребенок, у кого-то десять), жесткое кодирование десяти колонок в тексте запроса приведет к появлению пустых колонок у большинства участников. Решением станет программная сборка строки запроса.

Посмотрим на шаги:

1. Делаем предварительный запрос с использованием группировки по участнику и функции КОЛИЧЕСТВО(ДатаРождения). Сортируем результат по убыванию количества и берем ПЕРВЫЕ 1. Это даст нам максимальное количество дат у одного участника в текущей базе.
2. Запускаем цикл Для от 1 до полученного максимума. Внутри цикла формируем куски текста запроса: "МАКСИМУМ(ВЫБОР КОГДА НомерСтроки = " + Сч + " ТОГДА ДатаРождения ИНАЧЕ NULL КОНЕЦ) КАК Дата" + Сч.
3. Добавляем эти сформированные строки в основной текст запроса и выполняем его.

Решение 5: Сворачивание строк для визуального представления

Иногда разворачивание по колонкам требуется только для того, чтобы вывести данные на экран или в печатную форму в одну ячейку, и сложная математика с отдельными датами дальше не предполагается.

Если вы используете платформу версии 8.3.21 и выше, воспользуемся новой функцией языка запросов СОЕДИНИТЬСТРОКИ(). Эта функция позволяет сгруппировать данные по участнику и свернуть все даты рождения в одну текстовую колонку, разделив их заданным символом, например, запятой или переводом строки.

Выбор оптимального решения зависит от конкретных требований вашей задачи: нужно ли это для отчета (тогда выбираем СКД), важна ли производительность на тысячах записей (тогда используем ТЗ) или требуется гибкость в количестве колонок (тогда применяем динамический запрос).