Как преобразовать реквизиты элемента справочника из колонок в строки в запросе 1С?

← На главную

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

Например, если у нас есть справочник Справочник.Компьютеры с реквизитами Модель, ЧастотаПроцессора, ОбъемПамяти и т.д., и мы хотим получить для одного конкретного компьютера результат в виде:

| Характеристика | Значение | |---------------------|-----------------| | Модель | ПК-Офисный | | ЧастотаПроцессора | 3.2 ГГц | | ОбъемПамяти | 16 ГБ | | РазмерДиска | 512 ГБ | | Цена | 50000 | | СкоростьПривода | DVD-RW |

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

Наш исходный запрос и данные

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


ВЫБРАТЬ ПЕРВЫЕ 1
    Компьютеры.Ссылка
ПОМЕСТИТЬ КомпСМаксКодом
ИЗ
    Справочник.Компьютеры КАК Компьютеры
УПОРЯДОЧИТЬ ПО
    Компьютеры.Код УБЫВ
;

//////////////////////////////////////////////////////////////
// Этот запрос выбирает все необходимые реквизиты для одного компьютера
ВЫБРАТЬ
    Компьютеры.Модель,
    Компьютеры.ЧастотаПроцессора,
    Компьютеры.ОбъемПамяти,
    Компьютеры.РазмерДиска,
    Компьютеры.Цена,
    Компьютеры.СкоростьПривода
ИЗ
    Справочник.Компьютеры КАК Компьютеры
ГДЕ
    Компьютеры.Ссылка В (ВЫБРАТЬ КомпСМаксКодом.Ссылка ИЗ КомпСМаксКодом КАК КомпСМаксКодом)

Результатом этого запроса будет одна строка, содержащая значения всех перечисленных реквизитов в отдельных колонках. Наша задача — "развернуть" эту единственную строку таким образом, чтобы каждая пара (название реквизита, значение реквизита) стала отдельной строкой в результирующей таблице.

Решение: Транспонирование колонок в строки с помощью оператора ОБЪЕДИНИТЬ ВСЕ

Для выполнения этой операции, которую часто называют "unpivot" или транспонирование, мы будем использовать оператор ОБЪЕДИНИТЬ ВСЕ (эквивалент UNION ALL в стандартном SQL). Этот оператор позволяет объединять результаты нескольких запросов, добавляя строки одного запроса к строкам другого. Главное условие — все объединяемые запросы должны возвращать одинаковое количество колонок с совместимыми типами данных. В нашем случае мы будем получать две колонки: "Характеристика" и "Значение".

Рассмотрим по шагам, как мы можем построить такой запрос:

Для каждого реквизита, который мы хотим транспонировать, мы создаем отдельный подзапрос.
Каждый такой подзапрос будет выбирать две колонки:
- Первая колонка — это строковое представление имени реквизита (например, "Модель"). Мы указываем его как строковую константу.
- Вторая колонка — это фактическое значение реквизита для выбранного компьютера (например, Компьютеры.Модель).
Все эти подзапросы объединяем с помощью ОБЪЕДИНИТЬ ВСЕ.

Давайте посмотрим, как это будет выглядеть на примере наших характеристик компьютера:


ВЫБРАТЬ ПЕРВЫЕ 1
    Компьютеры.Ссылка
ПОМЕСТИТЬ КомпСМаксКодом
ИЗ
    Справочник.Компьютеры КАК Компьютеры
УПОРЯДОЧИТЬ ПО
    Компьютеры.Код УБЫВ
;

//////////////////////////////////////////////////////////////
// Основной запрос для транспонирования колонок в строки
ВЫБРАТЬ
    "Модель" КАК Характеристика,
    Компьютеры.Модель КАК Значение
ИЗ
    Справочник.Компьютеры КАК Компьютеры
ГДЕ
    Компьютеры.Ссылка В (ВЫБРАТЬ КомпСМаксКодом.Ссылка ИЗ КомпСМаксКодом КАК КомпСМаксКодом)

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    "ЧастотаПроцессора",
    Компьютеры.ЧастотаПроцессора
ИЗ
    Справочник.Компьютеры КАК Компьютеры
ГДЕ
    Компьютеры.Ссылка В (ВЫБРАТЬ КомпСМаксКодом.Ссылка ИЗ КомпСМаксКодом КАК КомпСМаксКодом)

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    "ОбъемПамяти",
    Компьютеры.ОбъемПамяти
ИЗ
    Справочник.Компьютеры КАК Компьютеры
ГДЕ
    Компьютеры.Ссылка В (ВЫБРАТЬ КомпСМаксКодом.Ссылка ИЗ КомпСМаксКодом КАК КомпСМаксКодом)

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    "РазмерДиска",
    Компьютеры.РазмерДиска
ИЗ
    Справочник.Компьютеры КАК Компьютеры
ГДЕ
    Компьютеры.Ссылка В (ВЫБРАТЬ КомпСМаксКодом.Ссылка ИЗ КомпСМаксКодом КАК КомпСМаксКодом)

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    "Цена",
    Компьютеры.Цена
ИЗ
    Справочник.Компьютеры КАК Компьютеры
ГДЕ
    Компьютеры.Ссылка В (ВЫБРАТЬ КомпСМаксКодом.Ссылка ИЗ КомпСМаксКодом КАК КомпСМаксКодом)

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    "СкоростьПривода",
    Компьютеры.СкоростьПривода
ИЗ
    Справочник.Компьютеры КАК Компьютеры
ГДЕ
    Компьютеры.Ссылка В (ВЫБРАТЬ КомпСМаксКодом.Ссылка ИЗ КомпСМаксКодом КАК КомпСМаксКодом)

В этом запросе мы видим несколько важных моментов:

Первый блок запроса (ВЫБРАТЬ ПЕРВЫЕ 1 ... ПОМЕСТИТЬ КомпСМаксКодом) создает временную таблицу, содержащую ссылку на один единственный компьютер, реквизиты которого мы будем транспонировать. Это позволяет нам не повторять условие выбора компьютера в каждом подзапросе.
Каждый последующий блок ВЫБРАТЬ ... ИЗ ... ГДЕ ... формирует одну строку результата для конкретной характеристики.
Мы явно задаем имя характеристики как строковую константу (например, "Модель"), которая станет значением поля Характеристика.
Значение соответствующего реквизита (например, Компьютеры.Модель) из справочника становится значением поля Значение.
Оператор ОБЪЕДИНИТЬ ВСЕ собирает все эти строки в единую результирующую таблицу. Использование ключевого слова ВСЕ важно, так как оно позволяет включать дублирующиеся строки (хотя в нашем случае для одного компьютера дубликатов не будет) и обычно работает быстрее, чем просто ОБЪЕДИНИТЬ, который по умолчанию удаляет дубликаты.

Таким образом, мы успешно преобразовали колонки нашего исходного запроса в строки с двумя полями: названием характеристики и её значением. Этот подход эффективен, когда количество реквизитов (колонок) фиксировано и заранее известно.

Ограничения подхода с ОБЪЕДИНИТЬ ВСЕ

Метод с использованием ОБЪЕДИНИТЬ ВСЕ хорошо подходит для транспонирования фиксированного набора колонок в строки. Однако у него есть свои особенности:

Повторяемость кода: Для каждого реквизита приходится писать практически идентичный блок запроса, что делает код достаточно объемным и менее читаемым, если реквизитов очень много (например, десятки или сотни).
Фиксированный набор характеристик: Если набор характеристик, которые нужно транспонировать, может меняться (например, они хранятся в регистре сведений с измерениями "ВидХарактеристики"), то этот подход не будет гибким. В таком случае нам пришлось бы генерировать текст запроса программно, что усложняет решение.
Совместимость типов: Все поля, которые в результате попадают в одну и ту же выходную колонку (например, "Значение"), должны быть совместимыми по типу. Если характеристики могут иметь разные типы (число, строка, дата), придется приводить их к одному общему типу, например, к строке, используя функцию ПРЕДСТАВЛЕНИЕ().

Теперь рассмотрим обратную задачу, которая чаще ассоциируется с термином "строку запроса в колонки" — это классический pivot (поворот).

Альтернативный подход: Поворот строк в колонки с помощью ВЫБОР КОГДА и агрегатных функций

Если задача состоит не в том, чтобы развернуть колонки существующей записи в строки, а, наоборот, в том, чтобы взять характеристики, хранящиеся в виде строк (например, в регистре сведений с полями Объект, ВидХарактеристики, ЗначениеХарактеристики), и представить их в виде отдельных колонок, то мы используем другой подход, схожий с оператором PIVOT в стандартном SQL.

В языке запросов 1С нет прямого аналога PIVOT, но мы можем эмулировать его с помощью комбинации оператора ВЫБОР КОГДА (эквивалент CASE WHEN) и агрегатных функций (МАКСИМУМ, МИНИМУМ) в сочетании с СГРУППИРОВАТЬ ПО.

Представим, что у нас есть регистр сведений РС_ХарактеристикиКомпьютеров со следующей структурой:

Измерение: Компьютер (ссылка на Справочник.Компьютеры)
Измерение: ВидХарактеристики (ссылка на Справочник.ВидыХарактеристик, где хранятся элементы "Модель", "ЧастотаПроцессора" и т.д.)
Ресурс: Значение (строка или число, в зависимости от типа характеристики)

И мы хотим получить результат:

| Компьютер | Модель | ЧастотаПроцессора | ОбъемПамяти | |-------------------|-----------------|-------------------|-------------| | Компьютер 1 | ПК-Офисный | 3.2 ГГц | 16 ГБ | | Компьютер 2 | Ноутбук Игровой | 4.5 ГГц | 32 ГБ |

Для решения этой задачи мы будем действовать следующим образом:

Выбираем основной объект для группировки — это будет Компьютер из нашего регистра сведений.
Для каждой характеристики, которую мы хотим видеть в отдельной колонке, мы создаем выражение с ВЫБОР КОГДА и агрегатной функцией.
Внутри ВЫБОР КОГДА мы проверяем, соответствует ли ВидХарактеристики той характеристике, которую мы хотим вывести в текущую колонку. Если да, то выбираем Значение, иначе — NULL.
Применяем агрегатную функцию (МАКСИМУМ или МИНИМУМ) к результату ВЫБОР КОГДА. Поскольку для каждого компьютера и каждого вида характеристики будет только одно не-NULL значение, агрегатная функция просто выберет его, игнорируя NULL из других строк группы.
Группируем результаты по Компьютер.

Пример запроса для такой ситуации:


ВЫБРАТЬ
    РС_ХарактеристикиКомпьютеров.Компьютер КАК Компьютер,
    МАКСИМУМ(ВЫБОР
        КОГДА РС_ХарактеристикиКомпьютеров.ВидХарактеристики = &ВидМодель
        ТОГДА РС_ХарактеристикиКомпьютеров.Значение
        ИНАЧЕ NULL
    КОНЕЦ) КАК Модель,
    МАКСИМУМ(ВЫБОР
        КОГДА РС_ХарактеристикиКомпьютеров.ВидХарактеристики = &ВидЧастотаПроцессора
        ТОГДА РС_ХарактеристикиКомпьютеров.Значение
        ИНАЧЕ NULL
    КОНЕЦ) КАК ЧастотаПроцессора,
    МАКСИМУМ(ВЫБОР
        КОГДА РС_ХарактеристикиКомпьютеров.ВидХарактеристики = &ВидОбъемПамяти
        ТОГДА РС_ХарактеристикиКомпьютеров.Значение
        ИНАЧЕ NULL
    КОНЕЦ) КАК ОбъемПамяти
ИЗ
    РегистрСведений.РС_ХарактеристикиКомпьютеров КАК РС_ХарактеристикиКомпьютеров
СГРУППИРОВАТЬ ПО
    РС_ХарактеристикиКомпьютеров.Компьютер

В этом запросе:

&ВидМодель, &ВидЧастотаПроцессора, &ВидОбъемПамяти — это параметры запроса, в которые мы передаем ссылки на соответствующие элементы справочника Справочник.ВидыХарактеристик.
Для каждого Компьютера (после группировки) запрос найдет строки, где ВидХарактеристики совпадает с заданным, возьмет Значение из этой строки, а для остальных видов характеристик в этой группе подставит NULL. Функция МАКСИМУМ затем выберет единственное не-NULL значение, фактически "поворачивая" строки в колонки.

Ограничения подхода с ВЫБОР КОГДА для поворота

Известный набор колонок: Этот метод требует, чтобы список колонок (видов характеристик), которые вы хотите получить в результате, был известен заранее при написании запроса. Если количество и названия колонок могут меняться, потребуется программно генерировать текст запроса.
Производительность: При очень большом количестве видов характеристик или большом объеме данных запрос может быть менее производительным из-за множества условий ВЫБОР КОГДА и необходимости группировки.
Типы данных: Если значения характеристик могут быть разных типов (строка, число, дата), то агрегатная функция будет работать только с совместимыми типами. Возможно, потребуется предварительное приведение значений к единому типу (например, к строке) с помощью ПРЕДСТАВЛЕНИЕ().

Когда использовать Систему Компоновки Данных (СКД)

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

Заключение

Мы рассмотрели два основных сценария преобразования данных в запросах 1С, которые часто путают под общим термином "строку в колонки":

Транспонирование (Unpivot) колонок в строки: Когда у нас есть запись с фиксированным набором реквизитов (колонок), и нам нужно представить каждую пару (имя реквизита, значение) как отдельную строку. Этот сценарий был основным в вопросе и эффективно решается с помощью оператора ОБЪЕДИНИТЬ ВСЕ.
Поворот (Pivot) строк в колонки: Когда характеристики хранятся в виде строк (например, в регистре сведений с измерениями ВидХарактеристики), и нам нужно превратить эти виды характеристик в отдельные колонки результирующей таблицы. Этот сценарий реализуется с использованием ВЫБОР КОГДА в сочетании с агрегатными функциями и СГРУППИРОВАТЬ ПО.

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