При разработке отчетов, обработок подбора или прайс-листов в 1С программисты часто сталкиваются с задачей транспонирования данных. Типичный пример — когда в регистре сведений цены хранятся в классическом «вертикальном» виде (каждая цена для каждого типа цен записана отдельной строкой), а пользователю на форме или в табличной части их нужно показать в «горизонтальном» виде (где каждый тип цен представлен отдельной колонкой).
В этой статье мы подробно разберем, как решить эту задачу различными методами. Мы проанализируем неоптимальные подходы, рассмотрим аналог конструкции PIVOT в запросах 1С, научимся строить такие запросы динамически под любое количество типов цен, а также разберем программный вывод результатов Системы компоновки данных (СКД) прямо в табличную часть формы.
Исходные данные в базе (например, в регистре сведений ЦеныНоменклатуры.СрезПоследних) хранятся в следующем виде:
Наша цель — получить на выходе плоскую таблицу следующей структуры:
Номенклатура | Закуп | Розница 1 | Розница 2
Номенклатура 1 | 192 | 208 | 215
Номенклатура 2 | 138 | 160 | 170
---
В сообщениях на форумах часто предлагают использовать множественные полные или левые соединения таблицы самой с собой (по количеству типов цен). Это крайне неоптимальный подход. Если у вас в базе 10 типов цен, то СУБД придется выполнить 10 тяжелых соединений больших таблиц, что приведет к колоссальному падению производительности на реальных базах данных.
Давайте рассмотрим гораздо более производительный и лаконичный вариант — использование агрегатной функции МАКСИМУМ в сочетании с условной конструкцией ВЫБОР. Этот подход заставляет СУБД просканировать таблицу выборки всего один раз.
Пример статического запроса, если типы цен нам известны заранее:
ВЫБРАТЬ
ЦеныСрез.Номенклатура КАК Номенклатура,
ЦеныСрез.Характеристика КАК Характеристика,
МАКСИМУМ(ВЫБОР КОГДА ЦеныСрез.ТипЦен = &ТипЗакуп ТОГДА ЦеныСрез.Цена ИНАЧЕ 0 КОНЕЦ) КАК ЦенаЗакуп,
МАКСИМУМ(ВЫБОР КОГДА ЦеныСрез.ТипЦен = &ТипРозница1 ТОГДА ЦеныСрез.Цена ИНАЧЕ 0 КОНЕЦ) КАК ЦенаРозница1,
МАКСИМУМ(ВЫБОР КОГДА ЦеныСрез.ТипЦен = &ТипРозница2 ТОГДА ЦеныСрез.Цена ИНАЧЕ 0 КОНЕЦ) КАК ЦенаРозница2
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ДатаСреза, ) КАК ЦеныСрез
СГРУППИРОВАТЬ ПО
ЦеныСрез.Номенклатура,
ЦеныСрез.Характеристика
Как это работает: конструкция ВЫБОР проверяет каждую строку на соответствие конкретному типу цен. Если условие выполняется, то возвращается цена, иначе — возвращается ноль. На этапе группировки функция МАКСИМУМ «схлопывает» все строки по конкретной номенклатуре (и характеристике), выбирая единственное ненулевое значение цены для каждого созданного поля. В результате мы получаем чистую горизонтальную строку с заполненными ценами.
---
В реальной жизни типы цен — это элементы справочника (например, ВидыЦен), и их состав регулярно меняется пользователями. Мы не можем зашить их в текст запроса «намертво». В таком случае нам необходимо формировать текст запроса динамически в коде 1С перед его выполнением.
Давайте разберем по шагам, как написать универсальный алгоритм формирования такого запроса:
// Шаг 1. Получаем список актуальных типов цен из справочника
ЗапросТипы = Новый Запрос;
ЗапросТипы.Текст =
"ВЫБРАТЬ
| ВидыЦен.Ссылка КАК Ссылка,
| ВидыЦен.Наименование КАК Наименование
|ИЗ
| Справочник.ВидыЦен КАК ВидыЦен
|ГДЕ
| НЕ ВидыЦен.ПометкаУдаления";
ТаблицаТипов = ЗапросТипы.Выполнить().Выгрузить();
// Шаг 2. Динамически собираем текст основного запроса
ТекстПолейЦен = "";
ПараметрыЗапроса = Новый Структура;
Для Индекс = 0 По ТаблицаТипов.Количество() - 1 Цикл
СтрокаТипа = ТаблицаТипов[Индекс];
// Формируем уникальное имя колонки без пробелов и спецсимволов
ИмяКолонки = "Цена_" + СтрЗаменить(СтрокаТипа.Ссылка.УникальныйИдентификатор(), "-", "_");
ИмяПараметра = "ПараметрТип_" + Индекс;
// Добавляем конструкцию ВЫБОР в текст запроса
ТекстПолейЦен = ТекстПолейЦен + ",
| МАКСИМУМ(ВЫБОР КОГДА ЦеныСрез.ТипЦен = &" + ИмяПараметра + " ТОГДА ЦеныСрез.Цена ИНАЧЕ 0 КОНЕЦ) КАК " + ИмяКолонки;
// Запоминаем параметры для передачи в основной запрос
ПараметрыЗапроса.Вставить(ИмяПараметра, СтрокаТипа.Ссылка);
КонецЦикла;
// Шаг 3. Собираем финальный текст запроса
ТекстОсновной =
"ВЫБРАТЬ
| ЦеныСрез.Номенклатура КАК Номенклатура,
| ЦеныСрез.Характеристика КАК Характеристика"
+ ТекстПолейЦен + "
|ИЗ
| РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ДатаСреза, ) КАК ЦеныСрез
|СГРУППИРОВАТЬ ПО
| ЦеныСрез.Номенклатура,
| ЦеныСрез.Характеристика";
// Шаг 4. Выполняем запрос и устанавливаем параметры
Запрос = Новый Запрос;
Запрос.Текст = ТекстОсновной;
Запрос.УстановитьПараметр("ДатаСреза", ТекущаяДата());
Для Каждого КлючИЗначение Из ПараметрыЗапроса Цикл
Запрос.УстановитьПараметр(КлючИЗначение.Ключ, КлючИЗначение.Значение);
КонецЦикла;
РезультатЗапроса = Запрос.Выполнить();
ТаблицаРезультат = РезультатЗапроса.Выгрузить();
Этот код абсолютно универсален. При добавлении нового типа цен в справочник программа автоматически сформирует для него новую колонку в результирующей таблице значений, не требуя вмешательства программиста в код запроса.
---
Система компоновки данных умеет разворачивать строки в колонки "из коробки" с помощью механизма кросс-таблиц. Но часто перед разработчиком встает задача: как полученную в СКД кросс-таблицу программно выгрузить в обычную ТаблицуЗначений или в табличную часть формы обработки?
Важное ограничение: напрямую выгрузить кросс-таблицу из СКД в коллекцию значений нельзя. Процессор вывода в коллекцию значений выдаст ошибку, если в структуре настроек схемы присутствуют горизонтальные группировки (колонки). Для программного получения такой таблицы нам придется использовать «плоские» настройки в СКД, а сам вывод осуществлять через генератор макета компоновки.
Рассмотрим детальный шаблон кода для выгрузки результата СКД в табличную часть формы обработки:
&НаСервере
Процедура ЗаполнитьТабличнуюЧастьНаСервере()
// Получаем схему компоновки данных из макета нашей обработки
СхемаКомпоновкиДанных = РеквизитФормыВЗначение("Объект").ПолучитьМакет("МакетСКД_Цены");
// Инициализируем компоновщик настроек
Настройки = СхемаКомпоновкиДанных.НастройкиПоУмолчанию;
// При необходимости устанавливаем параметры СКД программно
ПараметрДата = Настройки.ПараметрыДанных.Элементы.Найти("ДатаСреза");
Если ПараметрДата <> Неопределено Тогда
ПараметрДата.Значение = ТекущаяДата();
ПараметрДата.Использование = Истина;
КонецЕсли;
// Создаем компоновщик макета компоновки данных
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
// Важно: Указываем генератор макета для КОЛЛЕКЦИИ ЗНАЧЕНИЙ
МакетКомпоновки = КомпоновщикМакета.Выполнить(
СхемаКомпоновкиДанных,
Настройки, , ,
Тип("ГенераторМакетаКомпоновкиДанныхДляКоллекцииЗначений")
);
// Выполняем компоновку данных
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки);
// Выводим результат в ТаблицуЗначений
ТаблицаРезультат = Новый ТаблицаЗначений;
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВКоллекциюЗначений;
ПроцессорВывода.УстановитьОбъект(ТаблицаРезультат);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
// Переносим полученные данные в табличную часть формы
// Если колонки ТЧ на форме созданы динамически, используем ЗначениеВРеквизитФормы
ЗначениеВРеквизитФормы(ТаблицаРезультат, "Объект.ТабличнаяЧастьЦен");
КонецПроцедуры
---
Если вы не хотите усложнять текст запроса динамической сборкой строк и не хотите настраивать СКД, существует еще один надежный способ. Мы можем получить обычную «плоскую» таблицу простым и быстрым запросом, а затем транспонировать (развернуть) ее средствами встроенного языка 1С.
Давайте посмотрим, как реализовать этот алгоритм:
// Шаг 1. Получаем плоские данные из базы быстрым запросом
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ЦеныСрез.Номенклатура КАК Номенклатура,
| ЦеныСрез.ТипЦен КАК ТипЦен,
| ЦеныСрез.Цена КАК Цена
|ИЗ
| РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ДатаСреза, ) КАК ЦеныСрез";
Запрос.УстановитьПараметр("ДатаСреза", ТекущаяДата());
ПлоскаяТаблица = Запрос.Выполнить().Выгрузить();
// Шаг 2. Создаем новую Таблицу Значений для результата
ТаблицаРезультат = Новый ТаблицаЗначений;
ТаблицаРезультат.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
// Шаг 3. Собираем уникальные типы цен и создаем под них динамические колонки
СписокТиповЦен = Новый СписокЗначений;
Для Каждого СтрокаДанных Из ПлоскаяТаблица Цикл
Если СписокТиповЦен.НайтиПоЗначению(СтрокаДанных.ТипЦен) = Неопределено Тогда
СписокТиповЦен.Добавить(СтрокаДанных.ТипЦен);
// Превращаем наименование типа цены в валидное имя колонки
ИмяКолонки = "ТипЦена_" + СтрокаДанных.ТипЦен.УникальныйИдентификатор();
ИмяКолонки = СтрЗаменить(ИмяКолонки, "-", "_");
ТаблицаРезультат.Колонки.Добавить(ИмяКолонки, Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)), Строка(СтрокаДанных.ТипЦен));
КонецЕсли;
КонецЦикла;
// Шаг 4. Заполняем результирующую таблицу данными
Для Каждого СтрокаДанных Из ПлоскаяТаблица Цикл
// Ищем строку с этой номенклатурой в новой таблице или создаем ее
ПараметрыПоиска = Новый Структура("Номенклатура", СтрокаДанных.Номенклатура);
НайденныеСтроки = ТаблицаРезультат.НайтиСтроки(ПараметрыПоиска);
Если НайденныеСтроки.Количество() > 0 Тогда
СтрокаПриемник = НайденныеСтроки[0];
Иначе
СтрокаПриемник = ТаблицаРезультат.Добавить();
СтрокаПриемник.Номенклатура = СтрокаДанных.Номенклатура;
EndIf;
// Записываем цену в нужную динамическую колонку
ИмяКолонки = "ТипЦена_" + СтрокаДанных.ТипЦен.УникальныйИдентификатор();
ИмяКолонки = СтрЗаменить(ИмяКолонки, "-", "_");
СтрокаПриемник[ИмяКолонки] = СтрокаДанных.Цена;
КонецЦикла;
Преимущества данного метода: абсолютная простота чтения и отладки кода. Запрос остается элементарным и не нагружает сервер СУБД сложными группировками или соединениями. Вся постобработка происходит в оперативной памяти сервера приложений 1С, что на средних объемах данных (до нескольких десятков тысяч строк) работает мгновенно.
---
Мы проанализировали четыре практических способа разворачивания строк с ценами в горизонтальные колонки. Какой из них выбрать в реальном проекте?