Oracle развернуть строку в столбец
Обновлено: 01.07.2024
как я могу просто переключать столбцы со строками в SQL? Есть ли простая команда для транспонирования?
ie повернуть этот результат:
PIVOT кажется слишком сложным для этого сценария.
существует несколько способов преобразования этих данных. В своем первоначальном посте вы заявили, что PIVOT кажется слишком сложным для этого сценария, но он может быть очень легко наносится как с помощью UNPIVOT и PIVOT функции в SQL Server.
однако, если у вас нет доступа к этим функциям, это может быть реплицировано с помощью UNION ALL до UNPIVOT а затем агрегатная функция с CASE заявление PIVOT :
создать Таблица:
соединение все, агрегат и версия случая:
посмотреть SQL Скрипка с демо
The UNION ALL выполняет UNPIVOT данных путем преобразования столбцов Paul, John, Tim, Eric в отдельных строках. Затем вы применяете агрегатную функцию sum() С case утверждение, чтобы получить новые столбцы для каждого color .
преобразование и сводных статических Версия:
и UNPIVOT и PIVOT функции в SQL server делают это преобразование намного проще. Если вы знаете все значения, которые вы хотите преобразовать, вы можете жестко закодировать их в статической версии, чтобы получить результат:
посмотреть SQL Скрипка с демо
внутренний запрос с UNPIVOT выполняет ту же функцию, что и UNION ALL . Он берет список столбцов и превращает его в строки, PIVOT затем выполняет окончательное преобразование в столбцы.
Динамическая Версия Pivot:
если у вас есть неизвестное количество столбцов ( Paul, John, Tim, Eric в вашем примере), а затем неизвестное количество цветов для преобразования вы можете использовать динамический sql для создания списка в UNPIVOT а то PIVOT :
посмотреть SQL Скрипка с демо
динамическая версия запрашивает оба yourtable а то sys.columns таблица для создания список элементов UNPIVOT и PIVOT . Это затем добавляется в строку запроса для выполнения. Плюсом динамической версии является то, что у вас есть изменяющийся список colors и/или names это создаст список во время выполнения.
все три запроса приведут к одному и тому же результату:
обычно это требует, чтобы вы заранее знали все метки столбцов и строк. Как вы можете видеть в приведенном ниже запросе, все метки перечислены в их полностью в операциях UNPIVOT и (re)PIVOT.
Настройка схемы MS SQL Server 2012:
запрос 1:
результаты:
Дополнительная Информация:
- дана таблица имя, вы можете определить все имена столбцов из sys.колонки или для XML обмана с помощью local-name ().
- вы также можете создать список различных цветов (или значений для одного столбца), используя для XML.
- вышеизложенное может быть объединено в динамический пакет sql для обработки любой таблицы.
вертикальное расширение
похоже на PIVOT, курсор имеет динамическую возможность добавлять больше строк по мере расширения набора данных, чтобы включить больше номеров политик.
горизонтальное расширение
в отличие от PIVOT, курсор выделяется в этой области, поскольку он может расширяться, чтобы включить новый добавленный документ, не изменяя сценарий.
расстройства
основное ограничение транспонирования строк в столбцы с помощью Курсор является недостатком, который связан с использованием курсоров в целом – они приходят при значительных затратах на производительность. Это происходит потому, что курсор создает отдельный запрос для каждой следующей операции выборки.
вертикальное расширение
подобно PIVOT и Курсору, недавно добавленные политики могут быть получены в XML-версии сценария без изменения исходного сценария.
горизонтальное расширение
В отличие от оси, недавно добавленные документы могут отображаться без изменения сценария.
расстройства
с точки зрения ввода – вывода статистика XML – версии скрипта почти аналогична сводной-единственное отличие заключается в том, что XML имеет второе сканирование таблицы dtTranspose, но на этот раз из логического кэша чтения данных.
на основе этого решение С bluefeet вот хранимая процедура, которая использует динамический sql для создания транспонированной таблицы. Он требует, чтобы все поля были числовыми, за исключением транспонированного столбца (столбец, который будет заголовком в результирующей таблице):
вы можете проверить его с помощью таблицы, поставляемой с этой командой:
я делаю UnPivot сначала и сохранение результатов в CTE и с помощью CTE на Pivot операции.
демо
добавление к потрясающему ответу @Paco Zarate выше, если вы хотите транспонировать таблицу, которая имеет несколько типов столбцов, затем добавьте это в конец строки 39, поэтому она только транспонирует int столбцы:
вот полный запрос, который был изменен:
найти другие system_type_id ' s, запустите это:
таким образом, преобразуйте все данные из полей(столбцов) в таблице в запись (строку).
До версии 11 задача транспонирования решалась довольно сложно.
В версии 11 в синтаксисе оператора SELECTпоявились фразы PIVOT и UNPIVOT , которые выполняют такое транспонирование прозрачно и просто.
План запроса получается простой и эффективный -- запрос будет хорошо выполняться даже на больших объёмах. Прежние варианты с самосоединением таблиц были бы менее эффективны
Приведу пример использования:
Имеем таблицу курсов валют к рублю:
В 11g это можно сделать таким запросом:
select * from (select ISO_CODE , RATE_DATE , RATE_VALUE from EXCH_RATES )pivot (max ( RATE_VALUE )
for ISO_CODE in ( 'EUR' as RATE_EUR , 'UAH' as RATE_UAH , 'USD' as RATE_USD )
)
UPDATE 15.06.2015
Как транспонировать несколько полей ?
В приведённом выше примере мы транспонировали только одно поле -- RATE_VALUE превратилось в RATE_EUR, RATE_UAH, RATE_USD
Но бывают задачи, когда нужно транспонировать несколько полей, ниже я привожу пример, связанный тоже с курсами валют, но курсы взяты с рынка Форекс, и там строка курса содержит несколько значений OPEN (значение на момент начала интервала), MIN (минимальное, которое достигалось в интервале), MAX (максимальное, которое достигалось в интервале), CLOSE (каким значением курса интервал закончился), VOLUME (объём операций)
В этом случае, названия колонок нам нужно собрать перемножением множества значений ('OPEN', 'MIN', 'MAX', 'CLOSE', 'VOLUME') на множество значений названий пар валют
Этот запрос можно написать так, что в каждой колонке сначала будет название пары валют (поле из FOR) и через подчёркивание название поля, которое мы транспонируем
Сначала приведу запрос
select * from (select * from RATES)
pivot (avg (QOPEN) as QOPEN ,
avg (QMIN) as QMIN ,
avg (QMAX) as QMAX ,
avg (QCLOSE) as QCLOSE ,
avg (QVOLUME) as QVOLUME
for (CURPAIR) in ('EURGBP' as EURGBP
, 'EURPLN' as EURPLN
, 'EURUSD' as EURUSD)
)
order by 1
Алиасы полей, помеченные зелёным фоном -- обязательны
Вот таблица для тестирования
create table RATES (
CURPAIR varchar2(16) not null,
SNAPDATE date not null,
QOPEN number not null,
QMAX number not null,
QMIN number not null,
QCLOSE number not null,
QVOLUME number not null);
Oracle PIVOT позволяет написать перекрестный запрос таблицы, начал использоваться в Oracle 11g. Это означает, что вы можете объединить свои результаты и повернуть строки в столбцы.
Синтаксис
Синтаксис для оператора PIVOT в Oracle/PLSQL:
SELECT * FROM(
SELECT column1, column2
FROM tables
WHERE conditions
)
PIVOT
(
aggregate_function(column2)
FOR column2
IN ( expr1, expr2, . expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];
Параметры или аргументы
Это может быть функция, такая, как SUM, COUNT, MIN, MAX или AVG.
IN ( expr1 , expr2 , . expr_n )
Список значений для поворота column2 в заголовке кросс-табличного результата запроса.
Подзапрос может быть использован вместо списка значений. В этом случае результаты подзапроса будут использоваться для определения значений для поворота column2 в заголовке кросс-табличного результата запроса.
Применение
Оператор PIVOT может использоваться в следующих версиях Oracle/PLSQL:
Пример
Рассмотрим как использовать предложение PIVOT в Oracle.
Мы будем основывать наш пример на таблице под названием orders со следующим определением:
Для того чтобы показать вам данные для этого примера, мы будем выбирать записи из таблицы orders со следующим запросом SELECT:
Это записи таблицы orders . Мы будем использовать эти записи, чтобы продемонстрировать, как работает оператор PIVOT:
| order_id | customer_ref | product_id |
|---|---|---|
| 50001 | SMITH | 10 |
| 50002 | SMITH | 20 |
| 50003 | ANDERSON | 30 |
| 50004 | ANDERSON | 40 |
| 50005 | JONES | 10 |
| 50006 | JONES | 20 |
| 50007 | SMITH | 20 |
| 50008 | SMITH | 10 |
| 50009 | SMITH | 20 |
Теперь, создадим кросс-табличный запрос, используя следующий оператор PIVOT:
В этом примере, оператор PIVOT будет возвращать следующие результаты:
| customer_ref | 10 | 20 | 30 |
|---|---|---|---|
| ANDERSON | 0 | 0 | 1 |
| JONES | 1 | 1 | 0 |
| SMITH | 2 | 3 | 0 |
Теперь давайте разберем оператор PIVOT и объясним, как он работает.
Специфика полей для включения
Во-первых, мы хотим указать, какие поля включить в кросс-таблице. В этом примере мы хотим включить поля customer_ref и product_id . Это делается с помощью следующей части запроса:
Вы можете перечислить столбцы, которые будут включены, в любом порядке.
Специфика агрегатной функции
Далее, при создании нашего запроса кросс-таблицы, нам необходимо указать агрегатную функцию. Вы можете использовать любую из функций, например: SUM, COUNT, MIN, MAX или AVG.
В этом примере мы будем использовать функцию COUNT. Это позволит подсчитать количество значений product_id , которые соответствуют нашим критериям. Это делается с помощью следующей части запроса:
Специфика PIVOT значений
Наконец, мы должны указать значения PIVOT, чтобы включить в результат. Они будут использоваться в качестве заголовков столбцов в нашем кросс-табличном запросе. Чтобы указать значения PIVOT, вы можете использовать либо список значений в скобках, либо подзапрос.
В этом примере мы будем возвращать только следующие значения product_id : 10, 20, 30. В нашем кросс-табличном запросе эти значения станут нашими заголовками столбцов. Кроме того, обратите внимание, что эти значения являются конечным списком значений product_id , и не обязательно будут содержать все возможные значения.
Это делается с помощью следующей части запроса:
Теперь, когда мы совместим все это вместе, мы получим следующую PIVOT таблицу:
Oracle строка в столбец динамически из преобразованного столбца
Win10，Oracle Database 11g r2，plsql 12。
1, метод фиксированного преобразования столбца
2, хранимая процедура обработки
1) вызвать хранимую процедуру
2) Проверьте указанный вид
3. Связь между двумя методами
На самом деле, этот принцип очень прост, то есть с помощью динамического SQL преобразовать количество столбцов, которые вы не хотите писать, или не определите, запрос для поиска, склейте его, а затем выполните объединенный SQL для создания представления.
Разместите данные и код, который я протестировал.
В конце было грустное яйцо, и те, кто слышал, были грустными, а те, кто слышал, плакали!
Метод, описанный выше, очень простая идея, но гусь! ! ! Прежде чем я нашел эту идею, я тупо сделал другую версию, более сложную версию. , , Это заняло почти полдня. Следуя этой мысли, как я был счастлив, когда впервые закончил, как грустно сейчас! ! Но я не могу сказать прямо, просто запишите это здесь, идея, вероятно, такова:
То же самое относится и к хранимой процедуре: запрос sql, переданный в источник данных, обрабатывается хранимой процедурой для объединения в полный sql, требуемый функцией pivot, а затем непосредственно выполняется для определения результата, вставки его в общую таблицу записей и создания представления. Укажите на эту часть данных.
Данные испытаний относятся к тем же данным, вставленным выше.
1. Первый параметр: обязательное Запрос источника данных не имеет ограничений на количество столбцов, но предпоследний столбец запроса - это столбец преобразования, а последний столбец - столбец данных;
2. Второй параметр:
1) может быть пустым Запрос sql, содержимое которого является столбцом для преобразования. Вы можете указать порядок заголовка и имя отображаемого заголовка. Когда он пуст, отличное значение берется из предпоследнего столбца запроса источника данных, и сортировка не гарантируется.
2) Результат запроса должен содержать три столбца. Первый столбец представляет собой последовательность числовых столбцов (если вам не нужен этот столбец, просто укажите число). Второй столбец соответствует значению столбца преобразования в источнике данных. Преобразованное имя заголовка (если имя не нужно менять, этот столбец совпадает со вторым столбцом).
3. Третий параметр: Может быть пустым , Контент - это имя представления, которое нужно проверить после обработки. Если он пуст, по умолчанию используется tmp_rowToCol.
1. Поскольку содержимое этого метода хранится в таблице, а затем указывается непосредственно на данные через представление, указанное представление Sale_RowToCol, специфический синтаксис создания, является следующим, tmp_RowToCol_XiaoXianNv является таблицей хранения данных, fbs = ' 1 'представляет фактические данные о преобразовании, а fguid является ключом для этого преобразования.
Давайте проверим фактические данные в этой таблице. Это таблица, которая будет создана при первом вызове хранимой процедуры. Имеется 203 поля, и наша хранимая процедура может передавать и хранить не более такого количества столбцов.
Тогда данные этой таблицы будут храниться все время, эмм . кажется бесполезным, но, может быть, однажды мозг накачается и надеется проверить, что это довольно интересно. , , (Сам не верю)
Подсветка csdn. , Не кажется дружелюбным
Ах да, в этой хранимой процедуре есть синтаксис для создания таблиц и представлений. Если у вашего пользователя нет разрешения, вам нужно использовать пользователя dba, чтобы дать разрешение:
Сперва мы узнаем, кто был в мужьях у этих красоток. А потом с помощью незамысловатых спецэффектов я вам покажу, в каком порядке они друг с другом бракосочетались. Так что юным девам эта статья будет особенно интересна.
Создадим и заполним базовую таблицу
| ID | Актриса | Мужья |
|---|---|---|
| 1 | Анджелина Джоли | Джонни Ли Миллер, Билли Боб Торнтон, Брэд Питт |
| 2 | Шарлиз Терон | |
| 3 | Пенелопа Крус | Хавьер Бардем |
Из таблицы видно, что Анжелика была замужем трижды. Ее мужья перечислены в колонке через разделитель в порядке очередности их бракосочетания с актрисой. Условимся, что разделитель — это запятая, а пробел после нее — просто мусор.
Лиза Терон вообще ни разу не была замужем (гражданские браки не в счет), и она, по всей видимости, до сих пор ждет своего айтишника. Так что следует взять это на заметку и как следует поторопиться — даме уже, без малого, 40.
Ну и Пенелопа Крус — замужем всего один раз. Какая скука.
Но это все прелюдия, а на деле нужно получить следующий результат
| Актриса | Муж | Номер мужа п/п |
|---|---|---|
| Анджелина Джоли | Джонни Ли Миллер | 1 |
| Анджелина Джоли | Билли Боб Торнтон | 2 |
| Анджелина Джоли | Брэд Питт | 3 |
| Шарлиз Терон | ||
| Пенелопа Крус | Хавьер Бардем | 1 |
По сути надо выполнить операцию, обратную группировке и агрегации функцией listagg.
Будем двигаться от простого к сложному. Для начала предлагаю рассмотреть похожую задачу — извлечение чисел из одиночной строки с разделителем в табличный набор.
- Генерируются новые строки с помощью connect by level.
- Через regexp_count вычисляется количество чисел в строке между разделителями – это количество определяет верхнюю границу для генератора строк.
- С помощью regexp_substr извлекаются числа из строки. Номер вхождения шаблона в строку (4-й аргумент регулярки) соответствует значению псевдостолбца rownum — номер п/п сгенерированной строки. Вместо rownum можно было использовать и level, результат был бы аналогичным.
В таблице HOLLYWOOD мы имеем дело не с последовательностью чисел, а с именами знатных мужей. Но их можно также подсчитать с помощью функции regexp_count и извлекать, используя функцию regexp_substr, согласно вышеописанной методе. Теперь нужно вспомнить рецепты маринования бананов и выбрать один из способов генерации строк, когда известно их будущее количество. Для примеров я воспользуюсь 3-м и 5-м способом. Но при выборе наиболее оптимального метода генерации нужно обратить внимание на коммент пользователя xtender.
Объединив подходы, получаем следующее.
Спецэффект № 1.
Всё ОК – девушки счастливы в браках. Все, кроме Лизы Терон. Для таких, как Лиза, в запросе я использовал nvl2.
Спецэффект № 2.
Это было решение через коллекции.
UPD: Нарисовался еще один замечательный Спецэффект № 3 для Oracle 12c
Очевидно, что вариантов решения задачи существует немало. Выбор того или иного варианта – дело вкуса и вопрос производительности. Если бы у меня был Оскар, я бы его не задумываясь вручил тому, кто предложит наиболее лаконичный с точки зрения кода и наиболее оптимальный с точки зрения производительности способ.
Читайте также: