В практике разработки на платформе 1С часто возникает задача, когда данные, хранящиеся в строках (например, характеристики товара в табличной части или регистре сведений), необходимо вывести в виде горизонтальной таблицы, где каждая характеристика становится отдельной колонкой. В SQL это называется операцией PIVOT, но в языке запросов 1С прямого оператора для этого не существует. Рассмотрим подробнее, как решить эту задачу различными методами, проанализируем производительность и выберем оптимальную архитектуру хранения данных.
Это классический способ транспонирования данных внутри одного запроса. Основная идея заключается в том, чтобы сгруппировать данные по объекту (номенклатуре) и для каждой нужной колонки создать выражение, которое "выцепит" конкретное значение свойства. Разберем этот метод на примере.
Предположим, у нас есть данные о характеристиках в регистре сведений ДополнительныеСведения. Нам нужно получить таблицу, где в строках будут объекты, а в столбцах — значения свойств "Цвет" и "Размер". Проанализируем структуру такого запроса:
ВЫБРАТЬ
Сведения.Объект КАК Объект,
МАКСИМУМ(ВЫБОР
КОГДА Сведения.Свойство.Наименование = "Цена"
ТОГДА Сведения.Значение
ИНАЧЕ NULL
КОНЕЦ) КАК Цена,
МАКСИМУМ(ВЫБОР
КОГДА Сведения.Свойство.Наименование = "Цвет"
ТОГДА Сведения.Значение
ИНАЧЕ NULL
КОНЕЦ) КАК Цвет,
МАКСИМУМ(ВЫБОР
КОГДА Сведения.Свойство.Наименование = "Размер"
ТОГДА Сведения.Значение
ИНАЧЕ NULL
КОНЕЦ) КАК Размер
ИЗ
РегистрСведений.ДополнительныеСведения КАК Сведения
СГРУППИРОВАТЬ ПО
Сведения.Объект
Почему это работает? Мы используем функцию МАКСИМУМ, чтобы при группировке по объекту "схлопнуть" все строки в одну. Поскольку для конкретной колонки (например, "Цвет") условие ВЫБОР вернет значение только в одной строке, а во всех остальных будет NULL, функция МАКСИМУМ выберет именно то единственное значение, которое нам нужно.
Важно помнить, что этот метод требует заранее знать список характеристик. Если список свойств динамический, текст запроса придется формировать программно в цикле, обходя справочник характеристик.
Если ваша задача — вывести отчет, то использование "чистого" запроса для транспонирования является не самым эффективным путем. В 1С есть мощный инструмент — Система компоновки данных, которая умеет разворачивать строки в колонки автоматически. Рассмотрим, как это сделать правильно.
СправочникСсылка.Номенклатура), источник видов характеристик (например, ПланВидовХарактеристик.ДополнительныеРеквизитыИСведения) и таблицу значений (Регистр сведений).Номенклатура, а в колонки — поле Свойство (характеристика). В выбранные поля (ресурсы) добавьте поле Значение.Проанализируем преимущество этого подхода: система сама сформирует запрос к базе данных, оптимизирует его и динамически добавит столько колонок, сколько различных характеристик встретится в выборке. Вам не нужно переписывать код при добавлении нового цвета или размера в справочник.
В процессе обсуждения часто возникает вопрос: где лучше хранить характеристики — в табличной части справочника или в отдельном регистре? Выясним причину, по которой профессиональные разработчики выбирают связку ПланВидовХарактеристик (ПВХ) и РегистрСведений.
Преимущества регистра сведений перед табличной частью:
ПланВидовХарактеристик позволяет задать жесткую типизацию для каждого свойства. Например, свойство "Цвет" может быть только ссылкой на справочник Цвета, а "Дата покупки" — только типом Дата. Это избавляет от ошибок ввода данных.Посмотрим, как выглядит запрос для получения остатков вместе с характеристиками из регистра:
ВЫБРАТЬ
Остатки.Номенклатура КАК Номенклатура,
Остатки.КоличествоОстаток КАК Количество,
ЕСТЬNULL(Цвета.Значение, "Не указан") КАК Цвет,
ЕСТЬNULL(Размеры.Значение, 0) КАК Размер
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки КАК Остатки
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК Цвета
ПО Остатки.Номенклатура = Цвета.Объект
И Цвета.Свойство = &СвойствоЦвет
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК Размеры
ПО Остатки.Номенклатура = Размеры.Объект
И Размеры.Свойство = &СвойствоРазмер
Этот метод (левые соединения) хорош, если колонок немного (до 5-10). Если характеристик десятки, лучше вернуться к методу 1 с группировкой, так как большое количество соединений (JOIN) может существенно замедлить работу оптимизатора СУБД.
При использовании Плана видов характеристик поле Значение в регистре обычно имеет составной тип данных. В запросах это может приводить к замедлениям. Чтобы оптимизировать выполнение, всегда старайтесь использовать функцию ВЫРАЗИТЬ.
Например, если вы знаете, что характеристика "Размер" всегда является числом, пишите в запросе:
ВЫРАЗИТЬ(Сведения.Значение КАК ЧИСЛО(15, 2))
Это позволит СУБД более эффективно обрабатывать данные и правильно строить индексы при сортировке или фильтрации по этой колонке.
Для разовых задач в коде используйте конструкцию МАКСИМУМ(ВЫБОР...). Для создания отчетов с динамическим набором колонок всегда выбирайте СКД. И обязательно храните характеристики в Регистре сведений в связке с Планом видов характеристик — это стандарт де-факто для качественной разработки на платформе 1С.