Как транспонировать результат запроса в языке 1С?

← На главную

При разработке отчетов и механизмов анализа данных в системе 1С:Предприятие часто возникает задача транспонирования — поворота таблицы, когда значения из строк должны стать заголовками колонок. В классическом SQL для этого существует оператор PIVOT. Однако в языке запросов 1С прямой аналог этого оператора отсутствует. В рамках данной статьи мы подробно разберем, как обойти это ограничение, используя различные архитектурные подходы: от конструкций в самом запросе до механизмов Системы компоновки данных (СКД).

Метод 1: Эмуляция PIVOT через агрегатные функции и конструкцию ВЫБОР

Проанализируем наиболее распространенный способ решения задачи внутри одного запроса. Этот метод эффективен, когда набор значений, которые должны стать колонками, известен заранее (статический набор). Мы используем комбинацию группировки и условий ВЫБОР.

Рассмотрим ситуацию, когда нам нужно вывести продажи по месяцам в одну строку для каждой номенклатуры. Вместо того чтобы получать 12 строк на один товар, мы хотим получить одну строку с 12 колонками. Разберем пример реализации такого запроса:


ВЫБРАТЬ
    ПродажиОбороты.Номенклатура КАК Номенклатура,
    СУММА(ВЫБОР
            КОГДА МЕСЯЦ(ПродажиОбороты.Период) = 1
                ТОГДА ПродажиОбороты.КоличествоОборот
            ИНАЧЕ 0
        КОНЕЦ) КАК Январь,
    СУММА(ВЫБОР
            КОГДА МЕСЯЦ(ПродажиОбороты.Период) = 2
                ТОГДА ПродажиОбороты.КоличествоОборот
            ИНАЧЕ 0
        КОНЕЦ) КАК Февраль,
    СУММА(ВЫБОР
            КОГДА МЕСЯЦ(ПродажиОбороты.Период) = 3
                ТОГДА ПродажиОбороты.КоличествоОборот
            ИНАЧЕ 0
        КОНЕЦ) КАК Март
ИЗ
    РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК ПродажиОбороты

СГРУППИРОВАТЬ ПО
    ПродажиОбороты.Номенклатура

Важный момент: в данном примере мы используем функцию СУММА. Если в ячейке находится строковое значение или уникальный идентификатор, необходимо использовать функцию МАКСИМУМ. Этот подход позволяет "схлопнуть" все строки с нулевыми значениями, оставив только нужное числовое или текстовое значение в соответствующей колонке.

Метод 2: Программное построение динамического запроса

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

Выполняем предварительный запрос, чтобы получить список уникальных значений, которые станут именами колонок.
Проходим циклом по результату первого запроса и формируем строку текста для конструкции ВЫБОР.
Подставляем сформированную строку в основной шаблон запроса.

Посмотрим на пример кода, который формирует такой запрос:


// 1. Получаем список уникальных периодов (колонки)
ЗапросПериодов = Новый Запрос("ВЫБРАТЬ РАЗЛИЧНЫЕ Период ИЗ РегистрНакопления.Остатки");
ВыборкаПериодов = ЗапросПериодов.Выполнить().Выбрать();

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

// 2. Формируем итоговый текст
ТекстЗапроса = "ВЫБРАТЬ Номенклатура " + ТекстВыбор + " ИЗ Регистр... СГРУППИРОВАТЬ ПО Номенклатура";

Проанализируем этот метод: он гибок, но требует осторожности при именовании колонок (имена должны соответствовать правилам именования полей в 1С, не начинаться с цифр и не содержать пробелов).

Метод 3: Транспонирование через Систему компоновки данных (СКД)

Часто разработчики спрашивают: "Как СКД удается делать кросс-таблицы, если язык запросов этого не умеет?". Разберем этот механизм. СКД работает в два этапа: сначала получает линейные (плоские) данные из СУБД, а затем Процессор компоновки выполняет группировку и поворот данных в оперативной памяти сервера приложений.

Чтобы реализовать транспонирование в СКД, выполним следующие действия:

Создадим схему компоновки данных и добавим стандартный набор данных (запрос), который возвращает три колонки: Строка (Измерение 1), Колонка (Измерение 2) и Ресурс (Значение).
На закладке Настройки добавим элемент типа Таблица.
В Строки таблицы перетащим поле, которое должно идти по вертикали.
В Колонки таблицы перетащим поле, которое должно развернуться по горизонтали.
В Выбранные поля добавим ресурс (например, СуммаОборот).

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

Метод 4: Постобработка в Таблице Значений

Рассмотрим ситуацию, когда данные нужно не просто вывести в отчет, а передать дальше для программной обработки. В этом случае проще всего выгрузить результат обычного "вертикального" запроса в объект ТаблицаЗначений и перевернуть её программно.

Выполним следующий алгоритм:


// Исходная ТЗ имеет колонки: Объект, Параметр, Значение
ТЗ_Результат = Запрос.Выполнить().Выгрузить();

// Создаем новую ТЗ для транспонированных данных
ТЗ_Транспонированная = Новый ТаблицаЗначений;
ТЗ_Транспонированная.Колонки.Добавить("Объект");

// Наполняем колонками на основании уникальных параметров
Для Каждого СтрокаПараметр Из СписокПараметров Цикл
    ТЗ_Транспонированная.Колонки.Добавить(СтрокаПараметр.Имя);
КонецЦикла;

// Заполняем данными
Для Каждого СтрокаДанных Из ТЗ_Результат Цикл
    НоваяСтрока = ТЗ_Транспонированная.Добавить();
    // Логика поиска или соответствия строк и заполнения колонок...
КонецЦикла;

Этот метод дает максимальный контроль над процессом, но может быть менее производительным на огромных объемах данных по сравнению с агрегацией внутри запроса (Метод 1).

Резюме

Мы проанализировали основные способы имитации оператора PIVOT в 1С. Выясним, какой метод выбрать в зависимости от задачи:

Если колонки известны заранее и их немного — используйте ВЫБОР в запросе.
Если нужно вывести сложный отчет пользователю — используйте Таблицу в СКД.
Если нужно программно получить таблицу с неизвестным заранее количеством колонок — используйте Динамический текст запроса или Постобработку ТЗ.

Помните, что использование ЛЕВОЕ СОЕДИНЕНИЕ для транспонирования (когда каждая колонка — это отдельное соединение с таблицей) крайне не рекомендуется из-за резкого падения производительности при росте количества соединений. Группировка с условием ВЫБОР всегда будет работать быстрее на стороне СУБД.