1с как транспонировать таблицу в запросе
В статье показывается простой метод реализации аналога оператора PIVOT в запросе 1С без использования соединений.
Интересно было бы увидеть универсальный пример транспонирования данных с заранее неизвестными колонками.
А так конечно это в СКД за минуту решается
(1) Да будет Вам известно, что в T-SQL PIVOT не универсален и в нём так же указывается определённое число колонок.
Не всегда есть возможность использовать СКД. Например, когда нужно сделать транспонирование таблицы в запросе динамического списка, то СКД там не применишь.
А что касается универсального варианта, то только программной генерацией запроса. Сначала запросом получаем список всех свойств (будущих колонок транспонированной таблицы), программно формируем по ним запрос PIVOT, а затем выполняем его.
(3) По просьбам трудящихся, публикую пример программной генерации:
Восьмой; Hatson; ubnkfl; daho; kote; DrAku1a; Darklight; Cmapnep; eeeio; asg.aleks; user1503726; + 11 – Ответить
(5)Лучше было бы обернуть это всё в универсальную функцию - для получения текста запроса - в идеале 4-х отдельных секций текстов (выборка, источники, условие, группировка), а ну да - условий тут нет (хотя для универсальности не помешали бы) - тогда 3 секции. Но это уже так - просто пожелания от себя. Как минимум - это должно быть оформлено функция генерации текста запроса - вот тогда статья будет боле менее закончена. Для полноты можно только добавить вариант транспонирования для переданной таблицы значения - в виде параметра (когда нужно передать её во временную таблицу одновременно транспонировав)
Отдельной функцией можно было бы рассмотреть и динамическую - которая сама определяет колонки по значению таблицы данных из СУБД источника - получает фактические колонки и строит уже запрос на выборку
(7) Я такие задачи, если честно, решаю программной генерацией, и всем советую. Оно как-то прозрачнее, атомарнее и управляемее выходит. Удобнее для разработок и переделок.
Но только не ту жуть, как в (5), ибо это ппц, а нормально, по нужной коллекции (в обсуждаемом случае - по свойствам), в цикле, набрать текст запроса и айда вперёд. Ну и не NULL, конечно, лепить, а нормальное типизированное пустое значение. Если ПВХ, то это совсем просто, если реквизиты - ну, передавать пустышки нужных типов в параметры.
А, вот, ncerber это примерно и предложил.
(5)Тогда мы получим список всех колонок, даже если они будут пустые. Если в наименовании свойства будут пробелы, это будет нерабочий запрос.
(2) К сожалению, в доступных нам инструментах языка запросов 1С отсутствует работа с коллекцией колонок таблицы. Мы не можем даже получить их имена и количество. Поэтому говорить об универсальности исключительно в "чистом" запросе - бесполезно. Так что да, только программной генерацией.
Здесь СписокСвойств это список значений. А запятая в начале ставится, чтобы не задумываясь о том, где и сколько их нужно ставить.
ну статью можно было сократить до нескольких фраз.
формируем ручками - ищем максимум в запросе с фильтром по нужному свойству, либо в СКД.
(1)Вывод в таблицу вы имеете ввиду? Там у меня наблюдаются проблемы с производительностью в случае большого количества строк (несколько сотен). Время вывода отчета прямо пропорционально количеству строк и занимает десятки секунд(от 10 до 40 секунд в моем случае)
Извините, но это фигня, а не транспонирование. Вы хоть основы линейной алгебры почитайте, прежде чем термин использовать.
Настоящее транспонирование матрицы одним красивым запросом - это крайне нетривиальная задача, адекватного решения которой я пока не видел и сам не нашёл, хотя ищу уже года три.
Я вообще давно перестал понимать, почему за такой жуткий примитив столько плюсов накидывают. Уровень уже что, совсем "разжевать и в ротик положить", да.
(11) Описан достаточно тривиальный способ решения, я думаю каждый программист должен такое уметь, для остального есть Ildarovich
(13) Если способ тривиален, почему на Инфостарте - эта публикация, за всё существование "Инфостарта" появилась первой? А на действительно тривиальные вещи на Инфостарте, как правило, находится далеко не одна публикация.
(15)
Не знаю, но как минимум с начала своего программирования применяю такие способ, в основном в запросах со свойствами.
(11)
Yashazz , все люди разные. В мире разработчиков 1С, хватает тех, кто никогда не имел дела с реализацией SQL от Microsoft и слово Pivot им не известно.
Человеческая память ассоциативна и первая ассоциация, которая приходит в голову, когда с таблицей нужно сделать операцию, которая описана выше - это транспонирование.
Собственно, отдельного термина эта табличная операция и не имеет.
А транспонирование, как его определяет математика, в мире 1С, по большему счёту, и ненужно.
Термин "Транспонирование" был взят как русскоязычный аналог "Pivot".
Чтобы те, кто не знает слово "Pivot", но вспомнит что-то про транспонирование, вбив в поисковик запрос, могли найти эту публикацию.
На счёт примитива я не соглашусь.
Возьмите 10-к программистов, и дайте им эту задачку.
И я уверен, что большинство из них напишут запросы с "бородой" из ЛЕВЫХ СОЕДИНЕНИЙ.
Вот, на мой взгляд, неудачно был взят этот термин. Потому что сбивает с толку. Я, собственно, увидел заголовок, обрадовался - вот, мол, хоть кто-то решил эту задачу. А тут такая простая штука и никакого настоящего транспонирования. Конечно, я огорчился.
Возьмите 10-к программистов, и дайте им эту задачку.
И я уверен, что большинство из них напишут запросы с "бородой" из ЛЕВЫХ СОЕДИНЕНИЙ.
Значит, это не программисты, а, кхм, не будем говорить публично грубости, кто. Тупокодеры в лучшем случае.
Во-первых, уточняю терминологию, это о пользе. Во-вторых, написал, что нормально было бы в цикле обходить рассматриваемые свойства; в третьих, ИС это публичный ресурс, где постинги могут и не иметь иной цели, кроме высказывания мнения автора.
Вот, на мой взгляд, неудачно был взят этот термин. Потому что сбивает с толку. Я, собственно, увидел заголовок, обрадовался - вот, мол, хоть кто-то решил эту задачу.
Заголовок публикации начинается "Аналог PIVOT в запросе 1С", неужели Вы не знали, что Pivot не выполняет транспонирования таблицы, таким образом, каким его определяет математика?
Ведь с ваших же слов - это жуткий примитив.
Теперь насчёт транспонирования, как его определяет математика, если бы оно было реально нужно в СУБД, наверное во всех реализациях SQL появились бы те или иные команды, выполняющие такое действие с таблицами. Но за несколько десятков лет, такие команды так и не появились. Значит такое транспонирование в реальных задачах, стоящих перед СУБД ненужно.
И напоследок, введите в Яндексе поисковый запрос "pivot транспонирование" и Вы обнаружите энное количество публикаций, где эти два слова идут рука об руку. Потому что превращение строк в столбцы, которое происходит в результате применения Pivot, у программистов ассоциируется с операцией "транспонирование", пусть даже и не происходит классического транспонирования, определённого математикой.
Программирование - это обособленная область прикладной науки, а значит термины в ней вполне могут иметь свою трактовку, отличающуюся от других научных дисциплин.
(18) Ага, ага. Только вот Википедия об этом, к примеру, ничегошеньки не знает. Знает только классическое определение. А ссылка на "энное количество публикаций", увы, неубедительна. В интернете, к примеру, бессчётно случаев пунктуационных, синтаксических и орфографических ошибок, однако претендовать на "новые правила" они не могут, и на правила русского языка не влияют, оставаясь всего лишь ошибками - по незнанию и нежеланию знать.
С малой нужностью транспонирования в СУБД не спорю, я и не утверждал, что это каждодневно и прямо позарез. Я сказал ровно то, что сказал: что а) термин неудачен, б) ищу решение классической задачи запросом.
Уже делал и не раз, но как уже и не помню. Наверное, надо записать пример и где-нибудь сохранить :)
Итак, имеем:
К1,П1
К1,П2
К1, П3
К2, П1
К2, П2
…
Кх,П3
Надо получить
К1,П1,П2,П3
К2,П1,П2,П3
…
Кх,П1,П2,П3
Второй вариант
К1,П1,пусто,пусто
К1,пусто,П2,пусто
К1, пусто,пусто,П3
К2, П1,пусто,пусто
К2, пусто,П2,пусто
…
Кх,пусто,пусто,П3
Надо получить
К1,П1,П2,П3
К2,П1,П2,П3
…
Кх,П1,П2,П3
Поделитесь примерами запросов.
Спасибо.
(1) как делать "непрямыми" методами на 1С писать не буду, ибо это и так известно с небольшими вариациями на тему.
Для MSSQL достаточно будет сделать так:
1 2 3
1212121 444 5656
(1) В первом случае если количество П фиксировано, например не больше 3, как в примере, то можно сделать три левых соединения. Первым запросом выбрать и сгруппировать К, ну а потом цеплять левым соединением выборки по каждому полю.
(6) AnderWonder, совершенно верно, так и сделал. В реальности 4 вариации. Делаю полное соединение.
Но вылез баг, делаю на СКД, если в одном запросе делать более одного соединения то происходит что-то странное - часть данных теряется, хотя соединение и полное. Приходится каждое соединения оформлять в виде временной таблицы.
(10) AnderWonder, где там ошибаться? Там всего две строчки. Сейчас эксперименты ставлю - пытаюсь понять что происходит. Если раскопаю - сообщу.
если одном запросе делать более одного соединения то происходит что-то странное - часть данных теряется, хотя соединение и полное
(2) -FeNiX-, как это? Сгруппировал я по полю К, а что получилось в поле П?
Фигня получается в поле П.
Вообще-то должно получится ПолеК, ПолеП1, ПолеП2, ПолеП3
Можно поподробнее как надо сгруппировать, я наверное что-то не понял?
Спасибо.
Прямых способов решения для первого варианта нет. Самое простое - заполнить таблицу программным кодом, затем поместить ее как временную в запрос. Не мучайтесь, пытаясь сделать это в запросе - получится либо крайне ненаглядный текст запроса, либо динамическое формирование текста запроса, либо еще какие-то неприятности, а работать быстрее в итоге не станет.
Для второго случая, как правильно говорят, надо использовать группировку:
ВЫБРАТЬ
К КАК К,
МАКСИМУМ(П1) КАК П1,
МАКСИМУМ(П2) КАК П2,
.
МАКСИМУМ(ПN) КАК ПN
ИЗ .
СГРУППИРОВАТЬ ПО К
(4) juntatalor, делаю так же как и в первом случае - через соединение. Т.е. в первую временную таблицу выбираю К и П1, во вторую К и П2 и т.д. Потом соединяю таблицы по К.
Код в обработчике на кнопке на форме отчета:
Выгружаем все табличный документ:
Затем сохраняем во временный файл (потом удалим):
Затем читаем путем универсальной функции (подобная есть у любого в каком-то супер пупер общем модуле):
Задача такова что необходимо поменять местами Колонки со Строками. Условие Первая колонка значений это название колонок будущей таблицы значений.
Исходная таблица:
Индекс Колонка1 Колонка2 Колонка3 Колонка4
0 "Тип11" "Тип21" "Тип31" "Тип41"
1 "Тип12" "Тип22" "Тип32" "Тип42"
2 "Тип13" "Тип23" "Тип33" "Тип43"
3 "Тип14" "Тип24" "Тип34" "Тип44"
4 "Тип15" "Тип25" "Тип35" "Тип45"
Полученная таблица:
Индекс Тип11 Тип12 Тип13 Тип14 Тип15
0 "Тип11" "Тип12" "Тип13" "Тип14" "Тип15"
1 "Тип21" "Тип22" "Тип23" "Тип24" "Тип25"
2 "Тип31" "Тип32" "Тип33" "Тип34" "Тип35"
3 "Тип41" "Тип42" "Тип43" "Тип44" "Тип45"
Минусы :
1.того что имя колонок первоначальных не видно.
2. Значение исходной таблицы первой колонки должны быть типизированны для название колонок.
Код 1C v 8.х
Похожие FAQ
Еще в этой же категории
Как быстро вывести таблицу значений в табличный документ? 96
ТекстЗапроса = " ВЫБРАТЬ * ИЗ Справочник.Пользователи" ; ЗапросаДанных = Новый Запрос(ТекстЗапроса); РезЗапроса = ЗапросаДанных.Выполнить().Выгрузить(); ТабДокумент = Новый ТабличныйДокумент; Построитель = Новый Построит Создание Таблицы значений и описание типов значений ее колонок 29
ТаблицаПериодов = Новый ТаблицаЗначений; ТаблицаПериодов.Колонки.Добавить("НомерСтроки", Новый ОписаниеТипов("Число")); ТаблицаПериодов.Колонки.Добавить("Сотрудник", Новый ОписаниеТипов("СправочникСсылка.СотрудникиОрганизаций")); ТаблицаП Удаление строк Таблицы Значений 27
// 1. Удаление строк согласно условию НулевыеСтроки = ТаблицаПослеПодмен.НайтиСтроки(Новый Структура(" Сумма" ,0)); Для каждого СтрокаТаблицы Из НулевыеСтроки Цикл ТаблицаПослеПодмен.Удалить(СтрокаТаблицы) КонецЦикла; // Нужно оставить стр Как скопировать, выгрузить данные из одной таблицы значений в другую? 11
Как скопировать или выгрузить данные из одной таблицы значений в другую таблицу значений? 1. Добавление, копирование строк из одной таблицы значений в другую таблицу значений: Для каждого СтрокаТЗ Из Таблица1 Цикл ЗаполнитьЗначенияСвойств(Таблица Объединение 2 таблиц значений 11
// Объединить 2 таблицы значения // тзОсновная - таблица к которой будут изменяться данные // тзПрисоединяемая - таблица из которой будут браться данные // стОтборОдинаковых - стурктура со списком полей по которым определяеться одинаковость запис Посмотреть все в категории Работа с Таблицей Значений
Задача такова что необходимо поменять местами Колонки со Строками. Условие Первая колонка значений это название колонок будущей таблицы значений.
Исходная таблица:
Индекс Колонка1 Колонка2 Колонка3 Колонка4
0 "Тип11" "Тип21" "Тип31" "Тип41"
1 "Тип12" "Тип22" "Тип32" "Тип42"
2 "Тип13" "Тип23" "Тип33" "Тип43"
3 "Тип14" "Тип24" "Тип34" "Тип44"
4 "Тип15" "Тип25" "Тип35" "Тип45"
Полученная таблица:
Индекс Тип11 Тип12 Тип13 Тип14 Тип15
0 "Тип11" "Тип12" "Тип13" "Тип14" "Тип15"
1 "Тип21" "Тип22" "Тип23" "Тип24" "Тип25"
2 "Тип31" "Тип32" "Тип33" "Тип34" "Тип35"
3 "Тип41" "Тип42" "Тип43" "Тип44" "Тип45"
Минусы :
1.того что имя колонок первоначальных не видно.
2. Значение исходной таблицы первой колонки должны быть типизированны для название колонок.
Код 1C v 8.х
Похожие FAQ
Еще в этой же категории
Как быстро вывести таблицу значений в табличный документ? 96
ТекстЗапроса = " ВЫБРАТЬ * ИЗ Справочник.Пользователи" ; ЗапросаДанных = Новый Запрос(ТекстЗапроса); РезЗапроса = ЗапросаДанных.Выполнить().Выгрузить(); ТабДокумент = Новый ТабличныйДокумент; Построитель = Новый Построит Создание Таблицы значений и описание типов значений ее колонок 29
ТаблицаПериодов = Новый ТаблицаЗначений; ТаблицаПериодов.Колонки.Добавить("НомерСтроки", Новый ОписаниеТипов("Число")); ТаблицаПериодов.Колонки.Добавить("Сотрудник", Новый ОписаниеТипов("СправочникСсылка.СотрудникиОрганизаций")); ТаблицаП Удаление строк Таблицы Значений 27
// 1. Удаление строк согласно условию НулевыеСтроки = ТаблицаПослеПодмен.НайтиСтроки(Новый Структура(" Сумма" ,0)); Для каждого СтрокаТаблицы Из НулевыеСтроки Цикл ТаблицаПослеПодмен.Удалить(СтрокаТаблицы) КонецЦикла; // Нужно оставить стр Как скопировать, выгрузить данные из одной таблицы значений в другую? 11
Как скопировать или выгрузить данные из одной таблицы значений в другую таблицу значений? 1. Добавление, копирование строк из одной таблицы значений в другую таблицу значений: Для каждого СтрокаТЗ Из Таблица1 Цикл ЗаполнитьЗначенияСвойств(Таблица Объединение 2 таблиц значений 11
// Объединить 2 таблицы значения // тзОсновная - таблица к которой будут изменяться данные // тзПрисоединяемая - таблица из которой будут браться данные // стОтборОдинаковых - стурктура со списком полей по которым определяеться одинаковость запис Посмотреть все в категории Работа с Таблицей Значений
Пример Заполнения колонки одной таблицы по колонке другой, по типу ЛЕВОЕ СОЕДИНЕНИЕ
Это удобно когда например надо заполнить колонку не одним фиксированным значением, а в зависимости от значений в других колонках
Код 1C v 8.х
Запрос к таблице делается в 2 этапа:
Сначала она запросом помещается в МенеджерВременныхТаблиц, а затем вторым запросом к ней делается запрос!
Похожие FAQ
Как заполнить табличную часть формы программно? 8
Нужно по кнопке Заполнить - сформировать данные для заполнения табличных частей и заполнить их. Форма имеет вид: Рядом с кнопкой Записать и закрыть добавлена кнопка Заполнить документ , код ее команды: // Код заполнения ТЧ НаСервере П 17 правил для составления оптимального ЗАПРОСа к данным базы 1С 44
Для формирования и выполнения запросов к таблицам базы данных в платформе 1С используется специальный объект языка программирования Запрос . Создается этот объект вызовом конструкции Новый Запрос . Запрос удобно использовать, когда требуется получ 1C: Enterprise Development Tools 50
И вот случилось долгожданное: Вышел 1C: Enterprise Development Tools - это среда для разработки конфигурации в IDE Eclipse. С сайта 1С: « 1C:Enterprise Development Tools » – это инструмент нового поколения для разработчиков бизнес-приложений систем Cодержимое указанного ниже веб-сайта в этом приложении блокируется. Aboutsecurity_1cv8c.exe 1
Проблема: После обновления на 1С:Бухгалтерию предприятия 3-й версии, при нажатии на закладку командного интерфейса 1С:предприятие, выскакивает ошибка: Aboutsecurity_1cv8c.exe или Aboutsecurity_1cv8.exe «Содержимое указанного ниже веб-узла в э Google maps : вывод точек на карту и режим панорамы 7
В отличие от яндекс карт в GMaps можно использовать панорамы - за что им большой плюс! Надеюсь в яндексе прочитают этот пост и тоже когда-нибудь это сделают! Для клиента нужно было сделать вывод объектов на карту С возможностью просмотра панора Посмотреть все результаты поиска похожих
Еще в этой же категории
17 правил для составления оптимального ЗАПРОСа к данным базы 1С 44
Для формирования и выполнения запросов к таблицам базы данных в платформе 1С используется специальный объект языка программирования Запрос . Создается этот объект вызовом конструкции Новый Запрос . Запрос удобно использовать, когда требуется получ Что такое менеджер временных таблиц и как с ним работать? 11
В 1С есть такая замечательная вещь, как МенеджерВременныхТаблиц . Этот объект позволяет передавать временные таблицы из одного запроса в другой. То есть, мы можем создать временную таблицу в запросе, прерваться, выполнить какой-нибудь код, и продо Объектная модель схемы запроса 5
Периодически во встроенном языке возникает необходимость изменения текста запроса в зависимости от разных алгоритмических условий. Раньше подобная задача решалась путем непосредственного формирования нужного текста запроса в виде строки. А это не вс Как в запросе объединить несколько значений в один столбец отобрав по условию? 1
Бывают ситуации, особенно когда формируешь отчеты по проводкам, где надо вывести отчет по одному типу значений и эти значения могут быть в разных колонках регистра. В примере ниже, необходимо собрать отчет в разбивке по Номенклатурным группам. Про Курс по разработке и оптимизации запросов в 1С (Часть 1) 0
Часть 1. Основные конструкции языка запросов и их назначение • Назначение языка запросов • Структура запроса • Основные секции запроса и их назначение • Группировка результатов запроса • Фильтрация результатов запроса • Объединения и соединени Посмотреть все в категории Запросы
Читайте также: