Динамическое разбиение строк на столбцы в Oracle

У меня есть следующая таблица Oracle 10g, называемая _kv:

select * from _kv

ID       K       V
----     -----   -----
  1      name    Bob
  1      age     30
  1      gender  male
  2      name    Susan
  2      status  married

Я хотел бы повернуть свои ключи в столбцы, используя простой SQL (не PL/SQL), чтобы получившаяся таблица выглядела примерно так:

ID       NAME    AGE    GENDER  STATUS
----     -----   -----  ------  --------
  1      Bob      30     male 
  2      Susan                   married
  • Запрос должен иметь столько столбцов, сколько уникальных K существует в таблице (их не так много)
  • Невозможно узнать, какие столбцы могут существовать до запуска запроса.
  • Я пытаюсь не запускать первоначальный запрос для программной сборки окончательного запроса.
  • Пустые ячейки могут быть нулями или пустыми строками, не имеет значения.
  • Я использую Oracle 10g, но решение 11g также будет в порядке.

Есть много примеров, когда вы знаете, что могут быть вызваны ваши поворотные столбцы, но я просто не могу найти общее решающее решение для Oracle.

Спасибо!

Ответы

Ответ 1

Oracle 11g предоставляет операцию PIVOT, которая делает то, что вы хотите.

Решение Oracle 11g

select * from
(select id, k, v from _kv) 
pivot(max(v) for k in ('name', 'age', 'gender', 'status')

(Примечание: у меня нет копии 11g, чтобы проверить это, чтобы я не проверял ее функциональность)

Я получил это решение: http://orafaq.com/wiki/PIVOT

EDIT - опция pivot xml (также Oracle 11g)
По-видимому, существует также опция pivot xml, когда вы не знаете всех возможных заголовков столбцов, которые могут вам понадобиться. (см. раздел XML TYPE в нижней части страницы, расположенной в http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html)

select * from
(select id, k, v from _kv) 
pivot xml (max(v)
for k in (any) )

(Примечание. Как и раньше, у меня нет копии 11g, чтобы проверить это, поэтому я не проверял ее функциональность)

Edit2: Изменен v в операторах PIVOT и pivot xml на max(v), поскольку он должен быть агрегирован, как указано в одном из комментариев. Я также добавил предложение in, которое не является необязательным для PIVOT. Разумеется, необходимость указывать значения в предложении in ставит перед собой цель иметь полностью динамический сводный/кросс-таб-запрос, а также желание этого вопроса.

Ответ 2

Чтобы справиться с ситуациями, когда есть возможность нескольких значений (v в вашем примере), я использую PIVOT и LISTAGG:

SELECT * FROM
(
  SELECT id, k, v
  FROM _kv 
)
PIVOT 
(
  LISTAGG(v ,',') 
  WITHIN GROUP (ORDER BY k) 
  FOR k IN ('name', 'age','gender','status')
)
ORDER BY id;

Поскольку вам нужны динамические значения, используйте динамический SQL и передайте значения, определенные при запуске выбора данных таблицы, прежде чем вызывать оператор pivot.

Ответ 3

Бывает, что у вас есть задача на своде. Ниже работает для меня как проверено сейчас на 11g:

select * from
(
  select ID, COUNTRY_NAME, TOTAL_COUNT from ONE_TABLE 
) 
pivot(
  SUM(TOTAL_COUNT) for COUNTRY_NAME in (
    'Canada', 'USA', 'Mexico'
  )
);

Ответ 4

Прежде всего, динамический поворот с использованием pivot xml снова необходимо проанализировать. У нас есть другой способ сделать это, сохранив имена столбцов в переменной и передав их в динамический sql, как показано ниже.

У нас есть таблица, как показано ниже.

введите описание изображения здесь

Если нам нужно показать значения в столбце YR как имена столбцов и значения в этих столбцах из QTY, тогда мы можем использовать приведенный ниже код.

declare
  sqlqry clob;
  cols clob;
begin
  select listagg('''' || YR || ''' as "' || YR || '"', ',') within group (order by YR)
  into   cols
  from   (select distinct YR from EMPLOYEE);


  sqlqry :=
  '      
  select * from
  (
      select *
      from EMPLOYEE
  )
  pivot
  (
    MIN(QTY) for YR in (' || cols  || ')
  )';

  execute immediate sqlqry;
end;
/

РЕЗУЛЬТАТ

введите описание изображения здесь