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

← На главную

При разработке отчетов и обработок часто возникает задача развернуть данные из плоского регистра сведений в строковый вид (сделать Pivot / транспонирование). Типичный пример — регистр РегистрСведений.Штрихкоды (или РегистрСведений.ШтрихкодыНоменклатуры), где у одной номенклатуры может храниться несколько записей с разными типами штрихкодов (например, EAN13 и собственный внутренний код), а в отчете требуется получить одну строку на товар с отдельными колонками для каждого типа кода.

Разберем типичные ошибки при составлении такого запроса и рассмотрим правильные варианты реализации задачи.

В чем заключается ошибка базового подхода

Рассмотрим, с чем сталкивается разработчик при первой попытке решить задачу "в лоб" через конструкцию ВЫБОР КОГДА без агрегирования:


ВЫБРАТЬ
    Штрихкоды.Владелец КАК Номенклатура,
    ВЫБОР
        КОГДА Штрихкоды.ТипШтрихкода.Наименование = "Рощ"
            ТОГДА Штрихкоды.Штрихкод
        ИНАЧЕ ""
    КОНЕЦ КАК Штрихкод1,
    ВЫБОР
        КОГДА Штрихкоды.ТипШтрихкода.Наименование = "EAN13"
            ТОГДА Штрихкоды.Штрихкод
        ИНАЧЕ ""
    КОНЕЦ КАК Штрихкод2
ИЗ
    РегистрСведений.Штрихкоды КАК Штрихкоды

Проанализируем, почему этот запрос возвращает две строки на один товар вместо одной желаемой. В регистре сведений каждая запись представляет собой отдельную строку. Когда СУБД обрабатывает запись с кодом EAN13, в колонку Штрихкод2 попадает значение, а в Штрихкод1 — пустая строка. Для второй записи того же товара формируется противоположный результат. В итоге запрос возвращает несколько строк для одной позиции номенклатуры.

Кроме того, обращение через точку Штрихкоды.ТипШтрихкода.Наименование порождает неявное левое соединение и ухудшает производительность. Правильнее передавать тип через параметр или использовать литерал предопределенного значения.

Вариант 1. Условная агрегация с группировкой (Рекомендуемый способ)

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

Разберем по шагам текст готового запроса:


ВЫБРАТЬ
    Номенклатура.Ссылка КАК Номенклатура,
    МАКСИМУМ(ВЫБОР
        КОГДА Штрихкоды.ТипШтрихкода = &ТипШК_Рощ
            ТОГДА Штрихкоды.Штрихкод
        ИНАЧЕ ""
    КОНЕЦ) КАК ШК_Рощ,
    МАКСИМУМ(ВЫБОР
        КОГДА Штрихкоды.ТипШтрихкода = &ТипШК_EAN13
            ТОГДА Штрихкоды.Штрихкод
        ИНАЧЕ ""
    КОНЕЦ) КАК ШК_EAN13
ИЗ
    Справочник.Номенклатура КАК Номенклатура
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Штрихкоды КАК Штрихкоды
        ПО (Штрихкоды.Владелец = Номенклатура.Ссылка)
ГДЕ
    НЕ Номенклатура.ЭтоГруппа
    И НЕ Номенклатура.ПометкаУдаления
СГРУППИРОВАТЬ ПО
    Номенклатура.Ссылка

Как работает данный алгоритм:

Конструкция ВЫБОР КОГДА отсекает неподходящие типы для каждого поля, возвращая либо конкретный штрихкод, либо пустую строку.
Секция СГРУППИРОВАТЬ ПО объединяет все строки по конкретной ссылке номенклатуры.
Функция МАКСИМУМ() выбирает непустую строку из всех записей регистра для данного типа кода.

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

Вариант 2. Множественное левое соединение

Если требуется строго связать номенклатуру с отдельными срезами регистра под каждый тип штрихкода без условных агрегатов, можно использовать повторное соединение таблицы регистра:


ВЫБРАТЬ
    Номенклатура.Ссылка КАК Номенклатура,
    ЕСТЬNULL(Штрихкоды1.Штрихкод, "") КАК ШК_Рощ,
    ЕСТЬNULL(Штрихкоды2.Штрихкод, "") КАК ШК_EAN13
ИЗ
    Справочник.Номенклатура КАК Номенклатура
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Штрихкоды КАК Штрихкоды1
            ПО (Штрихкоды1.Владелец = Номенклатура.Ссылка)
                И (Штрихкоды1.ТипШтрихкода = &ТипШК_Рощ)
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Штрихкоды КАК Штрихкоды2
            ПО (Штрихкоды2.Владелец = Номенклатура.Ссылка)
                И (Штрихкоды2.ТипШтрихкода = &ТипШК_EAN13)
ГДЕ
    НЕ Номенклатура.ЭтоГруппа
    И НЕ Номенклатура.ПометкаУдаления

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

Вариант 3. Реализация с помощью СКД (Система компоновки данных)

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

В запросе СКД выбираем стандартные поля: Номенклатура, ТипШтрихкода, Штрихкод.
На вкладке Настройки создаем структуру типа Таблица.
В Строки таблицы добавляем группировку по полю Номенклатура.
В Колонки таблицы добавляем группировку по полю ТипШтрихкода.
На вкладке Ресурсы для поля Штрихкод прописываем выражение: МАКСИМУМ(Штрихкод) или СоединитьСтроки(Массив(Штрихкод), ", ").

Использование функции СоединитьСтроки() в СКД позволяет корректно вывести через запятую абсолютно все штрихкоды, даже если у одного товара зарегистрировано несколько кодов одинакового типа.