Dynamic Rows -> Columns

Здравейте,

имам едно въпросче за една заявка и се надявам все някой да може да помогне. Ето и какво ми представлява таблицата (базата е Oracle 10g):

ERROR_STATS:
type NUMBER
message VARCHAR2
error_date DATE

Трябва ми заявка, която да изглежда така горе-долу:

TYPE | MESSAGE | MONTH1_CNT | MONTH2_CNT |...|MONTHn_CNT ,

където МОNTH1 и MONTHn са месеците от старт-дата до край-дата, зададени като параметри. Използвам това, за да взема месеците:

SELECT ADD_MONTHS(TRUNC(:p_start_date,'MM'),L-1) AS MONTHS
FROM (SELECT LEVEL L
FROM DUAL
CONNECT BY LEVEL <= MONTHS_BETWEEN(ADD_MONTHS(TRUNC(:p_end_date,'MM'),1),TRUNC(:p_start_date,'MM')));

(незнам дали е от полза)

Направих заявка, която ми изкарва:

ТYPE | MESSAGE | MONTH | CNT

но не знам как да прехвърля редовете в колони. Търсих из нет-а, но не намерих нищо за променлив брой редове.

Идеи?

Благодаря предварително!

Трябва да си поиграеш с case.

select type,

          message,

          case when month = 'month1' then cnt

                  else null

          end as month1_cnt,

          case when month = 'month2' then cnt

                  else null

          end as month2_cnt,

... 

...

можеш да помислиш за динамичен SQL i функция, която връща таблица.

General Protection Fault in module 0xDEEBAA. Рестартирайте държавата!

Нещо не разбрах...(ама простено мие, че днес е сефтето след отпуска)

иде реч за тия месеци....
предполагам, че грешка от тип 10 (примерно) с месидж "грешка....", ще се повтаря много, много пъти. Т.е за тази грешка ще имаш повторение 20 пъти с различна дата....
и искаш????

тази грешка в кои месеци се появява и евентуално колко часа. Щото от DATE можеш да си вземеш и часа...

Горе долу :-)

Идеята е следната: имам WebApp, което при exception лог-ва в база. И искам репорт за грешките по месеци, т.е. 10 за май, 13 за юни, т.н. Но искам заявката да приема два параметъра - старт дата и енд дата, т.е. да зима месеците между тях ключително и да си прави групирането. Ватианта от предния пост за съжаления не става, понеже незнам точния брой на колоните, а с PL/SQL функция и return type table може и да стане, трябва да го пробвам.

Ето ти едно решение

SELECT ES.TYPE,ES.MESSAGE,TO_NUMBER(TO_CHAR(ES.ERROR_DATE,'mm')) MONTH, COUNT(ES.TYPE) ERRORSPERMOUNTH
FROM ERROR_STATS ES
where es.error_date between :p_startdate and :p_enddate
GROUP BY ES.TYPE,ES.MESSAGE,TO_NUMBER(TO_CHAR(ES.ERROR_DATE,'mm'))
ORDER BY TO_NUMBER(TO_CHAR(ES.ERROR_DATE,'mm'))

Пробвай го...

Да, това изкарва точно данните, които ми трябват, но не и в точния формат. Искам след като са групирани по type и message, за всеки месец да се добави колона и съответната стоийност в нея, т.е. за един тип, едно съобщение да има един единствен ред, с n на брой колони, символизиращи месеците. Проблема се явява от динамичността на броя n на месеците. Явно трябва да се прибегне до PL/SQL и функция, връщаща table.

Ми селекта ми горе си е баш PL/SQL
Няма никакъв проблем и какво значение колко ще са месеците?
Просто се изисква малко повечко игра и мислене

Сега и аз се боря с един шибан PACKAGE. КАто го напиша и тествам ще ти драсна за идеята ми...

Aз доколкото разбрах човека иска месеците да са му колони, а сумите в 1 ред.
Т.е. нещо такова:

june | july | aug | sept | oct | nov | dec

  23      2    21     28    11     9    15

General Protection Fault in module 0xDEEBAA. Рестартирайте държавата!

Toчно това искам :-)

Пробвах с PL/SQL функция, но тъй като трябва да променям типа на резултата, предпочитам да е по друг начин. Идеи?

Не съм запознат с Оракъл, затова една идея по принцип
Направи функция, която ти връща ред:
type NUMBER
message VARCHAR2
CntsByMonth varchar(колкото ти трябва)

като в CntsByMonth записваш стойностите на бройките като ползваш някакъв разделител (напр. #9) по който да си ги разделиш на клиента.

Ми кофтито е, че искаш да ти връща динамично различен брой месеци.
Иначе транспозицията е лесна.
Можеш да ползваш reference cursor (как се викаше: май слабо типизиран...) за да стартираш динамичен sql statement.
Всъщност отговора на въпроса ти може да се съдържа в това: какво ще правиш по-късно с тези данни? Ако връщането на курсор те урежда: генерираш си в една процедура в един varchar2 един динамичен sql и го пускаш на ей-тази процедура:

create or replace procedure reference_cursor(sql_select in varchar2, cur out sys_refcursor)

authid current_user 

as

begin

    open cur for sql_select; 

end reference_cursor;

/

Първия параметър ти е sql, втория ти връща курсора. Понеже SQL-a ти е стринг, може да бъде доволно дълъг (е.. до 32000 байта, ако не се лъжа), и динамично можеш да нацвъкаш вътре толкова кейсове (виж първия ми постинг), колкото месеци имаш. Върнатия курсор ще съдържа данните точно каквито ги искаш. Само въпроса е, дали курсор като резултат те урежда.

General Protection Fault in module 0xDEEBAA. Рестартирайте държавата!