Oracle
SQL

Транспонирование строк в столбцы

Опубликовано: 30 апр 04
Рейтинг:

Автор: Violina
Прислал: Violina

Допустим имеется таблица с данными о продажах

create table sales(
product varchar(20),
amount int,
dt date
);

необходимо создать отчёт/запрос в виде

PRODUCT          JAN  FEB  MAR  APR  MAI  JUN  JUL  AUG  SEP  OCT  NOV  DEC
--------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----

...              ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...

Заполняем таблицу тестовыми данными

insert into sales
(select
   'Scanner',
   round(dbms_random.value*20+1),
   trunc(sysdate, 'yy')+dbms_random.value*350
 from all_objects where rownum < 600
);

insert into sales
(select
   'Printer',
   round(dbms_random.value*20+1),
   trunc(sysdate, 'yy')+dbms_random.value*350
 from all_objects where rownum < 300
);

Задаём форматирование результатов для sqlplus, чтобы данные одной записи умещались в одну строку

column product format a15
column Jan format 999
column Feb format 999
column Mar format 999
column Apr format 999
column Mai format 999
column Jun format 999
column Jul format 999
column Aug format 999
column Sep format 999
column Oct format 999
column Nov format 999
column Dec format 999

Применяем метод, описанный во второй книге Тома Кайта:

select
product,
min(decode(mn, 1, sm, NULL)) Jan,
min(decode(mn, 2, sm, NULL)) Feb,
min(decode(mn, 3, sm, NULL)) Mar,
min(decode(mn, 4, sm, NULL)) Apr,
min(decode(mn, 5, sm, NULL)) Mai,
min(decode(mn, 6, sm, NULL)) Jun,
min(decode(mn, 7, sm, NULL)) Jul,
min(decode(mn, 8, sm, NULL)) Aug,
min(decode(mn, 9, sm, NULL)) Sep,
min(decode(mn, 10, sm, NULL)) Oct,
min(decode(mn, 11, sm, NULL)) Nov,
min(decode(mn, 12, sm, NULL)) Dec
from
 (
  select
  product,
  to_number(to_char(dt, 'MM')) mn,
  sum(amount) sm
  from
  sales
  group by product, to_number(to_char(dt, 'MM'))
 )
group by product;

PRODUCT          JAN  FEB  MAR  APR  MAI  JUN  JUL  AUG  SEP  OCT  NOV  DEC
--------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----

Printer          213  120  426  255  305  349  267  313  282  304  231  129
Scanner          630  582  595  446  622  651  395  579  728  484  573  381

Комментарии


  • неплохо, но жаль что честное ( когда кол-во записей наперёд неизвестно) транспонирование невозможно

  • pvasea, замени min в верхнем запросе поставь SUM
    или используй такой запрос
    select
    product,
    SUM(decode(to_number(to_char(dt, 'MM')), 1, amount, NULL)) Jan,
    SUM(decode(to_number(to_char(dt, 'MM')), 2, amount, NULL)) Feb,
    SUM(decode(to_number(to_char(dt, 'MM')), 3, amount, NULL)) Mar,
    SUM(decode(to_number(to_char(dt, 'MM')), 4, amount, NULL)) Apr,
    SUM(decode(to_number(to_char(dt, 'MM')), 5, amount, NULL)) Mai,
    SUM(decode(to_number(to_char(dt, 'MM')), 6, amount, NULL)) Jun,
    SUM(decode(to_number(to_char(dt, 'MM')), 7, amount, NULL)) Jul,
    SUM(decode(to_number(to_char(dt, 'MM')), 8, amount, NULL)) Aug,
    SUM(decode(to_number(to_char(dt, 'MM')), 9, amount, NULL)) Sep,
    SUM(decode(to_number(to_char(dt, 'MM')), 10, amount, NULL)) Oct,
    SUM(decode(to_number(to_char(dt, 'MM')), 11, amount, NULL)) Nov,
    SUM(decode(to_number(to_char(dt, 'MM')), 12, amount, NULL)) Dec
    from sales
    group by product

  • pvasea, замени min в верхнем запросе поставь SUM
    или используй такой запрос
    select
    product,
    SUM(decode(to_number(to_char(dt, 'MM')), 1, amount, NULL)) Jan,
    SUM(decode(to_number(to_char(dt, 'MM')), 2, amount, NULL)) Feb,
    SUM(decode(to_number(to_char(dt, 'MM')), 3, amount, NULL)) Mar,
    SUM(decode(to_number(to_char(dt, 'MM')), 4, amount, NULL)) Apr,
    SUM(decode(to_number(to_char(dt, 'MM')), 5, amount, NULL)) Mai,
    SUM(decode(to_number(to_char(dt, 'MM')), 6, amount, NULL)) Jun,
    SUM(decode(to_number(to_char(dt, 'MM')), 7, amount, NULL)) Jul,
    SUM(decode(to_number(to_char(dt, 'MM')), 8, amount, NULL)) Aug,
    SUM(decode(to_number(to_char(dt, 'MM')), 9, amount, NULL)) Sep,
    SUM(decode(to_number(to_char(dt, 'MM')), 10, amount, NULL)) Oct,
    SUM(decode(to_number(to_char(dt, 'MM')), 11, amount, NULL)) Nov,
    SUM(decode(to_number(to_char(dt, 'MM')), 12, amount, NULL)) Dec
    from sales
    group by product

  • Применил ROLLUP для подсчёта итогов по каждому месяцу .
    select
    product,
    min(decode(mn, 1, sm, NULL)) Jan,
    min(decode(mn, 2, sm, NULL)) Feb,
    min(decode(mn, 3, sm, NULL)) Mar,
    min(decode(mn, 4, sm, NULL)) Apr,
    min(decode(mn, 5, sm, NULL)) Mai,
    min(decode(mn, 6, sm, NULL)) Jun,
    min(decode(mn, 7, sm, NULL)) Jul,
    min(decode(mn, 8, sm, NULL)) Aug,
    min(decode(mn, 9, sm, NULL)) Sep,
    min(decode(mn, 10, sm, NULL)) Oct,
    min(decode(mn, 11, sm, NULL)) Nov,
    min(decode(mn, 12, sm, NULL)) Dec
    from
    (
    select
    product,
    to_number(to_char(dt, 'MM')) mn,
    sum(amount) sm
    from
    sales
    group by product, to_number(to_char(dt, 'MM'))
    )
    group by rollup (product);
    Но в итоге только значения для Printer, а сумма не считается.
    Вопрос- как подсчитать итоги в транспонированной таблице?

  • А какая реализация в слючае неизвестного числа столбцов? И со скоростью желательно чтоб проблем не сложилось при значительном объеме данных (> 1000000).

  • CASE аналогичен примеру с DECODE, который привел Владимор Конев 18.02.2005

  • еще если 9-ка или 10-ка можно использовать CASE

  • А если значения не числовые а текстовые?

  • 25 февраля 2005, 18:00 Oleksandr Sitnick

    Кстати, запрос, предложенный автором статьи, выполняется в 2 раза быстрее, чем вариант Владимира Конева.

  • Всё хорошо, только в заголовок необходимо добавить: Транспонирование строк в столбцы для заранее известного числа столбцов.

  • Нужная порой вещица

  • Нужная порой вещица

  • cупер запрос!! так держать

  • cупер запрос!! так держать

  • полезная мелочь!!!



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Oracle / SQL / Транспонирование строк в столбцы