select name, date, salary
from tabe1
поворот стоблца в oracle
подскажите
можно ли в выборке повернуть столбец в oracle на 90град.
допустим есть запрос
вывод данных будет такого типа:
NAME_____DATE_______SALARY
Петров___01.05.2009__1500
Петров___01.06.2009__1600
Сидоров__01.05.2009__1700
Сидоров__01.07.2009__2000
...
а хотелось бы так
Name_____01.05.2009__01.06.2009__01.07.2009 ...
Петров____1500_______1600_________0
Сидоров___1700_______0____________2000
...
можно ли в выборке повернуть столбец в oracle на 90град.
допустим есть запрос
Код:
вывод данных будет такого типа:
NAME_____DATE_______SALARY
Петров___01.05.2009__1500
Петров___01.06.2009__1600
Сидоров__01.05.2009__1700
Сидоров__01.07.2009__2000
...
а хотелось бы так
Name_____01.05.2009__01.06.2009__01.07.2009 ...
Петров____1500_______1600_________0
Сидоров___1700_______0____________2000
...
Средствами SQL нельзя... ну или почти нельзя Если количество столбцов-дат всегда фиксированное, то еще можно подумать, а в общем случае - нельзя.
Выхода вижу два:
1. Делать хранимую процедуру + временную таблицу
2. Попробовать извлечь выгоду из следующего представления на стадии отображения результатов
На выходе:
Оракла под рукой не было, проверял на postgresql. За исключением функции nvl (в postgresql похожий аналог coalesce) используемые конструкции вроде переносимы из postgresql в oracle.
Есть большой недостаток запроса. в allpairs считается полное декартово произведение (все возможные комбинации пар), что не есть оптимальный план выполнения запроса. С увеличением числа name и date - начнет притормаживать (а может даже и сильно притормаивать). Нужно бы потестировать, обязательно иметь индексы на поля name и date.
Ну как-то так :)
А вообще не нужно пытаться заставлять базу данных готовить данные в том виде, в котором их удобно показывать. С этой работой вполне успешно справляется клиентская часть.
Выхода вижу два:
1. Делать хранимую процедуру + временную таблицу
2. Попробовать извлечь выгоду из следующего представления на стадии отображения результатов
Код:
CREATE OR REPLACE VIEW allpairs AS
SELECT
allnames.name,
alldates.date
FROM
(SELECT DISTINCT name FROM table1 ORDER BY name) allnames,
(SELECT DISTINCT date FROM table1 ORDER BY date) alldates;
select
name, date, nvl(salary, 0)
from
allpairs left outer join table1 using (name, date)
order by
name, date;
SELECT
allnames.name,
alldates.date
FROM
(SELECT DISTINCT name FROM table1 ORDER BY name) allnames,
(SELECT DISTINCT date FROM table1 ORDER BY date) alldates;
select
name, date, nvl(salary, 0)
from
allpairs left outer join table1 using (name, date)
order by
name, date;
На выходе:
Код:
"Петров" "2009-01-05 00:00:00+10" 1500
"Петров" "2009-01-06 00:00:00+10" 1600
"Петров" "2009-01-07 00:00:00+10" 0
"Сидоров" "2009-01-05 00:00:00+10" 1700
"Сидоров" "2009-01-06 00:00:00+10" 0
"Сидоров" "2009-01-07 00:00:00+10" 2000
"Петров" "2009-01-06 00:00:00+10" 1600
"Петров" "2009-01-07 00:00:00+10" 0
"Сидоров" "2009-01-05 00:00:00+10" 1700
"Сидоров" "2009-01-06 00:00:00+10" 0
"Сидоров" "2009-01-07 00:00:00+10" 2000
Оракла под рукой не было, проверял на postgresql. За исключением функции nvl (в postgresql похожий аналог coalesce) используемые конструкции вроде переносимы из postgresql в oracle.
Есть большой недостаток запроса. в allpairs считается полное декартово произведение (все возможные комбинации пар), что не есть оптимальный план выполнения запроса. С увеличением числа name и date - начнет притормаживать (а может даже и сильно притормаивать). Нужно бы потестировать, обязательно иметь индексы на поля name и date.
Ну как-то так :)
А вообще не нужно пытаться заставлять базу данных готовить данные в том виде, в котором их удобно показывать. С этой работой вполне успешно справляется клиентская часть.
Спасибо за совет.
только привелегия для create увы мне недоступна. а если сделать декартово произведение во временной таблице, как это сделать?
используя конструкцию
только привелегия для create увы мне недоступна. а если сделать декартово произведение во временной таблице, как это сделать?
используя конструкцию
Код:
with table_name as (запрос, создающий временнную таблицу)
Цитата: devil_incarnate
только привелегия для create увы мне недоступна.
если не можете делать create, тогда пробуйте вот такой запрос (очевидно, запрос allpairs целиком подставляем внутрь основного запроса)
Код:
SELECT
allpairs.name,
allpairs.date,
NVL(table1.salary, 0)
FROM
(SELECT
allnames.name,
alldates.date
FROM
(SELECT DISTINCT name FROM table1 ORDER BY name) allnames,
(SELECT DISTINCT date FROM table1 ORDER BY date) alldates
) allpairs LEFT OUTER JOIN table1 USING (name, date)
ORDER BY
name,
date;
allpairs.name,
allpairs.date,
NVL(table1.salary, 0)
FROM
(SELECT
allnames.name,
alldates.date
FROM
(SELECT DISTINCT name FROM table1 ORDER BY name) allnames,
(SELECT DISTINCT date FROM table1 ORDER BY date) alldates
) allpairs LEFT OUTER JOIN table1 USING (name, date)
ORDER BY
name,
date;
allpairs и есть подзапрос, делающий декартово произведение
А ещё, если количество групп строго ограничено, можно использовать decode.
Если нужна универсальность, то лучше (как уже было упомянуто) использовать процедуру.
Код:
SELECT
name,
MAX(DECODE(TO_CHAR(date, 'MM'), '05', salary)) "01.05.2009",
MAX(DECODE(TO_CHAR(date, 'MM'), '06', salary)) "01.06.2009",
MAX(DECODE(TO_CHAR(date, 'MM'), '06', salary)) "01.07.2009"
FROM table1
GROUP BY name
name,
MAX(DECODE(TO_CHAR(date, 'MM'), '05', salary)) "01.05.2009",
MAX(DECODE(TO_CHAR(date, 'MM'), '06', salary)) "01.06.2009",
MAX(DECODE(TO_CHAR(date, 'MM'), '06', salary)) "01.07.2009"
FROM table1
GROUP BY name
Если нужна универсальность, то лучше (как уже было упомянуто) использовать процедуру.