Как перевернуть таблицу в sql
Простой способ транспонировать столбцы и строки в SQL?
Как мне просто переключать столбцы со строками в SQL? Есть ли простая команда для транспонирования?
т.е. превратить этот результат:
PIVOT кажется слишком сложным для этого сценария.
Однако, если у вас нет доступа к этим функциям, их можно воспроизвести с помощью функции UNION ALL to, UNPIVOT а затем агрегатной функции с CASE инструкцией для PIVOT :
Создать таблицу:
Версия Union All, Aggregate и CASE:
Статическая версия Unpivot и Pivot:
Оба UNPIVOT и PIVOT функция в сервере SQL сделать это преобразование гораздо проще. Если вы знаете все значения, которые хотите преобразовать, вы можете жестко закодировать их в статическую версию, чтобы получить результат:
Версия Dynamic Pivot:
Если у вас есть неизвестное количество столбцов ( Paul, John, Tim, Eric в вашем примере), а затем неизвестное количество цветов для преобразования, вы можете использовать динамический sql для создания списка, UNPIVOT а затем PIVOT :
Все три запроса дадут одинаковый результат:
Обычно это требует, чтобы вы знали ВСЕ метки столбцов И строк заранее. Как вы можете видеть в запросе ниже, все метки перечислены полностью как в операциях UNPIVOT, так и в операциях (re) PIVOT.
Настройка схемы MS SQL Server 2012 :
Запрос 1 :
Дополнительные замечания:
Я хотел бы указать еще несколько решений для транспонирования столбцов и строк в SQL.
Вертикальное расширение
Подобно PIVOT, курсор имеет динамическую возможность добавлять больше строк по мере того, как ваш набор данных расширяется и включает больше номеров политик.
Горизонтальное расширение
В отличие от PIVOT, курсор выделяется в этой области, так как он может расширяться для включения недавно добавленного документа без изменения сценария.
Анализ производительности
Решение XML для транспонирования строк в столбцы в основном является оптимальной версией PIVOT, поскольку оно устраняет ограничение динамического столбца.
Версия сценария XML устраняет это ограничение, используя комбинацию XML Path, динамического T-SQL и некоторых встроенных функций (например, STUFF, QUOTENAME).
Вертикальное расширение
Подобно PIVOT и Cursor, новые добавленные политики можно получить в XML-версии сценария без изменения исходного сценария.
Горизонтальное расширение
В отличие от PIVOT, недавно добавленные документы могут отображаться без изменения сценария.
Анализ производительности
Вы можете найти больше об этих решениях (включая некоторые актуальные примеры T-SQL) в этой статье: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/
На основе этого решения от bluefeet представлена хранимая процедура, которая использует динамический sql для создания транспонированной таблицы. Требуется, чтобы все поля были числовыми, кроме транспонированного столбца (столбца, который будет заголовком в результирующей таблице):
Вы можете проверить это с помощью таблицы, предоставленной с помощью этой команды:
Я делаю UnPivot сначала, сохраняю результаты CTE и использую CTE в Pivot работе.
Добавляя к потрясающему ответу @Paco Zarate выше, если вы хотите транспонировать таблицу, которая имеет несколько типов столбцов, добавьте это в конец строки 39, чтобы она перемещала только int столбцы:
Вот полный изменяемый запрос:
Мне нравится делиться кодом, который я использую для транспонирования разделенного текста на основе ответа + bluefeet. В этом подходе я реализован как процедура в MS SQL 2005
Я смешиваю это решение с информацией о том, как упорядочить строки без упорядочения по ( SQLAuthority.com ) и функцией разделения на MSDN ( social.msdn.microsoft.com )
Когда вы выполняете prodecure
вы получите следующий результат
Таким образом преобразовать все данные из файлов (столбцов) в таблицу в запись (строку).
Я смог использовать решение Пако Зарате, и оно прекрасно работает. Мне пришлось добавить одну строку («SET ANSI_WARNINGS ON»), но это может быть чем-то уникальным для того, как я ее использовал или называл. Возникла проблема с моим использованием, и я надеюсь, что кто-нибудь сможет мне с этим помочь:
Решение работает только с реальной таблицей SQL. Я пробовал это с временной таблицей, а также с таблицей (объявленной) в памяти, но она не работает с ними. Поэтому в вызывающем коде я создаю таблицу в своей базе данных SQL, а затем вызываю SQLTranspose. Опять же, отлично работает. Это то, чего я хочу. Вот моя проблема:
Чтобы общее решение было действительно динамичным, мне нужно создать эту таблицу, в которой я временно храню подготовленную информацию, которую я отправляю в SQLTranspose «на лету», а затем удалить эту таблицу после вызова SQLTranspose. Удаление таблицы представляет проблему с моим окончательным планом реализации. Код должен запускаться из приложения конечного пользователя (кнопка в форме / меню Microsoft Access). Когда я использую этот процесс SQL (создаю таблицу SQL, вызываю SQLTranspose, удаляю таблицу SQL), приложение конечного пользователя выдает ошибку, потому что используемая учетная запись SQL не имеет прав на удаление таблицы.
Я полагаю, что есть несколько возможных решений:
Найдите способ заставить SQLTranspose работать с временной таблицей или объявленной табличной переменной.
Придумайте еще один метод перестановки строк и столбцов, который не требует реальной таблицы SQL.
Я понимаю, что некоторые из них могут потребовать другой, отдельной темы и вопроса. Однако, поскольку есть вероятность, что одно решение может быть просто другим способом транспонирования строк и столбцов, я сделаю свой первый пост здесь, в этой теме.
РЕДАКТИРОВАТЬ: Я также заменил sum (value) на max (value) в 6-й строке с конца, как предложил Пако.
Я понял кое-что, что мне подходит. Не знаю, лучший это ответ или нет.
Я рассудил, что для моих целей разрешено создавать таблицу для учетной записи пользователя. Однако пользователь все еще не может удалить таблицу. Мое решение заключалось в создании схемы, в которой учетная запись пользователя авторизована. Затем всякий раз, когда я создаю, использую или удаляю эту таблицу, обращайтесь к ней с указанной схемой.
Сначала я ввел эту команду из учетной записи ‘sa’ или ‘sysadmin’: CREATE SCHEMA ro AUTHORIZATION
Каждый раз, когда я обращаюсь к своей таблице «tmpoutput», я указываю ее следующим образом:
PIVOT — поворот таблицы в T-SQL
В этой статье: о применении инструкций PIVOT и UNPIVOT языка T-SQL для разворота табличных данных.
Содержание
Лирическое вступление
Решив возобновить свои навыки владения SQL, продолжил начатое когда-то решение упражнений на замечательном сайте sql-ex.ru. В отличие от многих других ресурсов, где проводится тестирование путем выбора варианта среди ответов на вопрос, здесь решением является собственный SQL-запрос, который должен выдать верный набор данных на тестовой базе данных.
В одной из задач возникла необходимость отобразить результирующий набор данных зеркально относительно диагонали. На практике мне не довелось сталкиваться со случаями, когда подобный разворот необходимо делать именно средствами SQL. Обычно это решается несколько проще уже на наборе данных, который был возвращён средствами SQL. Хотя, слышал, что есть любители задавать подобную задачку на собеседованиях.
На sql-ex используется СУБД MS SQL Server, работающая с расширенным вариантом SQL — Transact-SQL (T-SQL). Гугль на запрос «T-SQL повернуть таблицу» сразу подсказал, что существует инструкция pivot, а последующее изучение этой темы — unpivot.
Ужас! Таково было моё впечатление после изучения документации и примеров использования. Синтаксис специфический и интуитивно непонятный, документация и имеющиеся примеры из одной колонки делают одну строку и наоборот. А как быть с несколькими сроками и столбцами? В редких случаях имеем примеры нескольких строк и колонок в результате, но полученных агрегатными функциями. А что делать, если меня в конкретном случае агрегирование не интересует? «Ну нипанятна!»
Скажу откровенно, как работает pivot, к моменту написания этих строк я так до конца и не разобрался. Тем не менее, попробую в процессе написания понять сам и как можно проще объяснить читателям.
Формулируем задачу
В общем случае задача достаточно проста для понимания. Из определенного исходного набора данных получить результирующий, зеркально отображенный относительно диагонали, например:
Мы видим, что есть набор данных, в котором число колонок не совпадает с числом строк (4×3), при этом колонки превращаются в строки и наоборот (3×4).
Надеюсь, что к концу статьи мы сможем проделать подобную операцию. К сожалению, одной простой командой такая транспозиция не произойдет.
Примеры будут приводится по структуре и данным из тестовой БД сайта sql-ex (другой возможности проверить запросы на СУБД MS SQL Server у меня просто нет).
Начнём с конца, с освоения unpivot, он мне показался проще.
UNPIVOT
UNPIVOT используется совместно с инструкцией SELECT и позволяет строку с данными развернуть в виде колонки.
Например, есть таблица Product, содержащая информацию о производителе, номере и типе продукции:
При выполнении следующего простейшего запроса:
В результате выполнения этого, пока еще непонятного, запроса получим:
Т. е. строка развернулась и стала вертикально. Имевшийся ранее селектовый запрос обёрнут новым, в котором присутствует блок unpivot. Синтаксис этого блока можно описать следующим образом:
Как можно догадаться, изменение первой строчки запроса
даст следующий результат:
Теперь рассмотрим работу с несколькими записями вложенного запроса. Для понимания вполне хватит двух. Изменим первичный запрос:
В поворачивающем запросе изменился только вложенный подзапрос t:
В результате его выполнения получим:
Явная бессмыслица — данные в таком виде вряд ли применимы (что получится, если добавить в конце запроса сортировку? Например: order by fields ).
Чтобы данным придать осмысленность, попробуем их представить в таком виде:
Здесь для каждой модели в отдельную колонку выведены характеристики «производитель» и «тип».
Если первую строку запроса изменить, добавив в нее поле model из вложенного запроса:
то при выполнении запроса возникнет ошибка — поле «модель» участвует в развороте данных и не может быть выведено в отдельную колонку. Что делать? Всего лишь исключить поле model из перечня в блоке unpivot. Получим следующий рабочий запрос:
Если в первую строку запроса добавить еще поле fields и изменить имена полей, получим:
и результат запроса:
Таким образом, UNPIVOT позволяет:
PIVOT
PIVOT является обратным по отношению к UNPIVOT, т. е. столбцы поворачивает в строки, но делает это несколько иначе, с использованием агрегатной функции.
Результат выполнения запроса:
Используемые в блоке pivot запроса поля model и maker, это поля, которые содержит исходный набор данных, таблица product. В перечислении maker in ([A], [B], [D]) значения «A», «B» и «D» — названия тех производителей из колонки maker, данные по которым необходимо вывести в виде отдельных колонок.
Здесь сразу заметно существенное ограничение в использовании pivot — с его помощью поворачивается не любой набор данных, а тот, из которого мы можем получить заранее оговорённый набор колонок (кварталы года, дни месяца, перечень конкретных компаний, сотрудников и т. п.).
При ближайшем рассмотрении тестового примера становится понятно, что в отличие от unpivot, осуществляющего «чистый» разворот, pivot в первую очередь предназначен для создания таких себе отчетов в более удобочитаемой форме, поэтому в нем и присутствует необходимость использования агрегатной функции.
В начале непонятно, откуда в примере взялась колонка type, если в запросе мы её нигде не использовали? Pivot делает отчет по тому набору данных, который ему передан. В примере он получил в качестве набора данных просто таблицу product, которая содержит три поля maker, model и type. Мы указали просчитать количество моделей по таким-то производителям, но поскольку в наборе данных есть еще поля, то pivot делает группировку и по всем остальным полям, оставляя их в итоговом наборе данных.
Т.е. с точки зрения полученных данных наш запрос полностью аналогичен такому:
отличается лишь представление данных:
Как быть, если мы хотим выбрать итог по производителям без учёта типа продукции? Ограничить набор данных требуемыми полями. Получим:
Мы ограничили набор исходных данных, с которыми мы работаем ( maker in (‘A’, ‘B’, ‘D’) ). Сразу уточню, что если команда pivot используется без параметра xml ( pivot xml ), то мы явно должны указать параметры IN. При выборке в формате xml, можем просто ввести any или выбрать значения обычным select. В результате получим:
Для придания человекочитаемости результату запроса я нарушил принципы нормализации, в колонке «Производитель:» внесено значение «Число моделей:» 🙂
Если не удовлетворяют имена колонок, полученные по значениям из исходного набора данных, то их можно переименовать обычным способом:
Таким образом, PIVOT позволяет:
Полный разворот
Работу PIVOT и UNPIVOT рассмотрели, но первоначально задекларированную задачу зеркально отобразить таблицу ещё не решили. Этим и займёмся.
Допустим, что в интернет-магазине есть функционал, обеспечивающий возможность показывать сравнительную характеристику нескольких выбранных пользователем ПК, при этом, характеристики каждого ПК выводятся в виде отдельной колонки, а на каждый вид характеристики выделена отдельная строчка.
Например, пользователь выбрал для сравнения ПК с кодами товара 1, 2, 4 и 7.
выдаст следующий набор данных:
Как можно заметить, существуют одинаковые модели, имеющие различные характеристики, поэтому сразу понятно, что для сравнения группировать характеристики следует не по модели, а по коду продукта.
Шаг 1. Вертикальное представление характеристик
На первом шаге получим характеристики в вертикальном представлении с использованием UNPIVOT.
Здесь все значения, попадающие в колонку характеристик, приведены к типу наибольшего из строковых полей model — varchar(50).
Чтобы значения можно было однозначно идентифицировать, выведены колонки с кодом продукта и названием характеристики.
При выполнении запроса получим следующие данные:
Шаг 2. Сведение характеристик в строки
На предыдущем шаге характеристики ПК были представлены в вертикальном виде, но в одной колонке для всех ПК. Осталось характеристики каждого ПК вывести в отдельную колонку.
Это умеет делать PIVOT, но есть одна проблема: PIVOT — конструкция, использующая агрегацию. На sql-ex я подсмотрел достаточно простое решение: поскольку для пары code+prop в нашем наборе данных существует только одно значение val, то при группировке по этим полям выражение max(val) выдаст само это значение 🙂
В результате получим:
Шаг 3. Улучшение визуального представления
Требуемые данные в нужной структуре представления получены. Что меня не устраивает — неинформативные заголовки колонок и неудобный для восприятия порядок сортировки характеристик. Сделаем незначительные доработки запроса:
В результате его выполнения получим:
Заключение
Инструкции PIVOT и UNPIVOT — достаточно мощный и интересный инструмент, расширяющий возможности SQL. Инструмент неидеальный, т.к. у него имеются определённые ограничения. Например, не представляется возможным повернуть набор данных, для которого значения ключевого поля и число записей (строк) заранее неизвестны. Но в случаях, подобных примеру «сравнительной характеристики», эти инструкции позволяют ещё на стороне сервера баз данных выполнить достаточно лаконичный запрос без значительных последующих преобразований данных на сервере приложений или клиенте.
Оператор PIVOT
Давайте рассмотрим такую задачу.
Для каждого производителя из таблицы Product определить число моделей каждого типа продукции.
Задачу можно решить стандартными средствами с использованием оператора CASE:
Теперь решение через PIVOT:
Надеюсь, что комментарии к коду достаточно понятны для того, чтобы написать оператор PIVOT без шпаргалки. Давайте попробуем.
Посчитать среднюю цену на ноутбуки в зависимости от размера экрана.
Задача элементарная и решается с помощью группировки:
|
А вот как можно повернуть эту таблицу с помощью PIVOT:
|
В отличие от сводных таблиц, в операторе PIVOT требуется явно перечислить столбцы для вывода. Это серьезное ограничение, т.к. для этого нужно знать характер данных, а значит и применять в приложениях этот оператор мы сможем, как правило, только к справочникам (вернее, к данным, которые берутся из справочников).
Если рассмотренных примеров покажется недостаточно, чтобы понять и использовать без затруднений этот оператор, я вернусь к нему, когда придумаю нетривиальные примеры, где использование оператора PIVOT позволяет существенно упростить код.
Простой способ транспонирования столбцов и строк в Sql?
как я могу просто переключать столбцы со строками в SQL? Есть ли простая команда для транспонирования?
ie повернуть этот результат:
PIVOT кажется слишком сложным для этого сценария.
8 ответов:
существует несколько способов преобразования этих данных. В своем первоначальном посте вы заявили, что PIVOT кажется слишком сложным для этого сценария, но он может быть очень легко наносится как с помощью UNPIVOT и PIVOT функции в SQL Server.
однако, если у вас нет доступа к этим функциям, это может быть реплицировано с помощью UNION ALL до UNPIVOT а затем агрегатная функция с CASE заявление PIVOT :
создать Таблица:
соединение все, агрегат и версия случая:
преобразование и сводных статических Версия:
и UNPIVOT и PIVOT функции в SQL server делают это преобразование намного проще. Если вы знаете все значения, которые вы хотите преобразовать, вы можете жестко закодировать их в статической версии, чтобы получить результат:
Динамическая Версия Pivot:
если у вас есть неизвестное количество столбцов ( Paul, John, Tim, Eric в вашем примере), а затем неизвестное количество цветов для преобразования вы можете использовать динамический sql для создания списка в UNPIVOT а то PIVOT :
все три запроса приведут к одному и тому же результату:
обычно это требует, чтобы вы заранее знали все метки столбцов и строк. Как вы можете видеть в приведенном ниже запросе, все метки перечислены в их полностью в операциях UNPIVOT и (re)PIVOT.
Настройка схемы MS SQL Server 2012:
запрос 1:
Дополнительная Информация:
Я хотел бы указать еще несколько решений для транспонирования столбцов и строк в SQL.
вертикальное расширение
похоже на PIVOT, курсор имеет динамическую возможность добавлять больше строк по мере расширения набора данных, чтобы включить больше номеров политик.
горизонтальное расширение
в отличие от PIVOT, курсор выделяется в этой области, поскольку он может расширяться, чтобы включить новый добавленный документ, не изменяя сценарий.
расстройства
основное ограничение транспонирования строк в столбцы с помощью Курсор является недостатком, который связан с использованием курсоров в целом – они приходят при значительных затратах на производительность. Это происходит потому, что курсор создает отдельный запрос для каждой следующей операции выборки.
XML-решение для транспонирования строк в столбцы в основном является оптимальной версией PIVOT в том, что оно обращается к ограничению динамического столбца.
версия XML сценарий устраняет это ограничение с помощью комбинации XML-пути, динамического T-SQL и некоторых встроенных функций (т. е. STUFF, QUOTENAME).
вертикальное расширение
подобно PIVOT и Курсору, недавно добавленные политики могут быть получены в XML-версии сценария без изменения исходного сценария.
горизонтальное расширение
В отличие от оси, недавно добавленные документы могут отображаться без изменения сценария.
расстройства
с точки зрения ввода – вывода статистика XML – версии скрипта почти аналогична сводной-единственное отличие заключается в том, что XML имеет второе сканирование таблицы dtTranspose, но на этот раз из логического кэша чтения данных.
вы можете найти еще несколько об этих решениях (включая некоторые фактические примеры T-SQL) в эта статья: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/
на основе этого решение С bluefeet вот хранимая процедура, которая использует динамический sql для создания транспонированной таблицы. Он требует, чтобы все поля были числовыми, за исключением транспонированного столбца (столбец, который будет заголовком в результирующей таблице):
вы можете проверить его с помощью таблицы, поставляемой с этой командой:
я делаю UnPivot сначала и сохранение результатов в CTE и с помощью CTE на Pivot операции.
добавление к потрясающему ответу @Paco Zarate выше, если вы хотите транспонировать таблицу, которая имеет несколько типов столбцов, затем добавьте это в конец строки 39, поэтому она только транспонирует int столбцы:
вот полный запрос, который был изменен:
найти другие system_type_id ‘ s, запустите это:
мне нравится делиться кодом, который я использую для транспонирования разделенного текста на основе ответа + bluefeet. В этом приближении я реализован как процедура в MS SQL 2005
Я смешиваю это решение с информацией о том, как упорядочить строки без порядка по (SQLAuthority.com) и функция разделения на MSDN (social.msdn.microsoft.com)
когда вы выполняете prodecure
вы получаете следующее результат
таким образом, преобразуйте все данные из полей(столбцов) в таблице в запись (строку).
CROSSTAB в PostgreSQL
Поясним сказанное на примере базы данных «Окраска».
Давайте для каждого квадрата просуммируем количество краски каждого цвета:
Здесь мы ограничились только квадратами с номерами в диапазоне 12-16, чтобы, с одной стороны, уменьшить вывод, а, с другой стороны, сделать вывод презентативным. Сортировка по цветам выполнена в порядке RGB. Вот результат:
|
|
Теперь с помощью CROSSTAB попытаемся написать запрос, который бы дал требуемый результат:
Здесь мы должны перечислить список столбцов с указанием их типа. При этом столбцы категорий могут быть перечислены не все. Посмотрим на результат (вы можете проверять запросы в консоли, выбрав для исполнения PostgreSQL):
|
Этот результат не вполне совпадает с ожидаемым. Напомним, что здесь важен только порядок. Если квадрат окрашивался только одним цветом, то значение (суммарный объем краски) попадет в первую категорию (у нас она называется R), каким бы этот единственный цвет ни был. Давайте перепишем запрос таким образом, чтобы он давал значения для всех цветов причем в нужном порядке. При этом отсутствующий цвет будем заменять NULL-значением. Чтобы добиться этого, добавим для каждого квадрата по одной строке каждого цвета со значением объема краски равным NULL: