Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Новый топик    Ответить
 Запрос с неизвестным числом колонок  [new]
DB2Adventurer
Member

Откуда:
Сообщений: 66
Есть таблица районов REGION(ID,NAME), таблица CUSTOMERS(ID, DISTRICT_ID, REG_DATE).
На этапе выполнения пользователь выбирает год по который нужно вывести распределение количества клиентов в каждом районе по годам регистрации.

DISTRICT/Year 2002 2003 2004
1 5 3 5
2 7 3 6
3 9 4 6

Можно запихать районы в качестве колонок, но их много и расписывать лень, к тому же отчет не смотрится. Какой подход вообще используется для решения таких задач? Посоветуйте пожалуйста.
11 май 07, 10:12    [4124120]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неизвестным числом колонок  [new]
DB2Adventurer
Member

Откуда:
Сообщений: 66
МОжет у кого-то есть пример процедуры?
11 май 07, 10:17    [4124147]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неизвестным числом колонок  [new]
gardenman
Member

Откуда: С-Петербург
Сообщений: 2347
Т.е. вы хотите получить crosstable?
11 май 07, 14:22    [4126211]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неизвестным числом колонок  [new]
gardenman
Member

Откуда: С-Петербург
Сообщений: 2347
Варианты:
1)Заюзать XML
2)Заюзать временные таблицы.
11 май 07, 14:24    [4126223]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неизвестным числом колонок  [new]
DB2Adventurer
Member

Откуда:
Сообщений: 66
gardenman
Т.е. вы хотите получить crosstable?

Да, кажется, это так называется, если не ошибаюсь. Я решил использовать временные таблицы, причем следующим образом: делать обычный запрос с фиксированным числом колонок по районам, затем делать транспонирование(т.е. перевести колонки в строки, а строки наоборот в колонки). Строк в первичном запросе будет меньше чем 255. Думаю это более-менее универсальное решение, не зависит от задачи. Так нормально или есть лучшие решения?

А как использовать XML? У меня v8.2. В этом случае куда смотреть?
14 май 07, 04:02    [4131959]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неизвестным числом колонок  [new]
gardenman
Member

Откуда: С-Петербург
Сообщений: 2347
В этом случае лучше смотреть на v9. ИМХО
14 май 07, 11:18    [4132950]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неизвестным числом колонок  [new]
Mark Barinstein
Member

Откуда: Москва
Сообщений: 4946
DB2Adventurer
таблица CUSTOMERS(ID, DISTRICT_ID, REG_DATE).
На этапе выполнения пользователь выбирает год по который нужно вывести распределение количества клиентов в каждом районе по годам регистрации.

DISTRICT/Year 2002 2003 2004
1 5 3 5
2 7 3 6
3 9 4 6
Я у себя реализовал это, используя процедурную логику, примерно так:
1. Выясняются все различные значения REG_DATE.
2. На основе 1 динамически строится запрос:
select 
  district_id
, count(case when reg_date=reg_date1 then id else cast(null as int) end) as reg_date1
...
, count(case when reg_date=reg_dateN then id else cast(null as int) end) as reg_dateN
group by district_id
Здесь reg_date1, ..., reg_dateN - все различные значения.
То, как они вставляются в динамический запрос, зависит от типа данных поля REG_DATE (т.е. если это строка, надо оборочивать ее в кавычки, если число - не надо и т.д.).
В примере предполагается, что это int.
15 май 07, 10:29    [4137394]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неизвестным числом колонок  [new]
DB2Adventurer
Member

Откуда:
Сообщений: 66
СПасибо всем за помощь. 2gardenman за что я узнал, что такое crosstable&pivoting. Тяжело искать не зная, как называется искомое :)
2Mark Barinstein за решение.

Сделал так:
CREATE PROCEDURE DB2ADMIN.REPORT_BY_YEAR ( IN p_begin INTEGER,
                                           IN p_end INTEGER)
    SPECIFIC DB2ADMIN.REPORT_BY_YEAR
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Хранимая процедура
    -- v_begin
    -- v_end
------------------------------------------------------------------------
P1: BEGIN
    DECLARE v_current INTEGER;
    DECLARE v_dynSQL     CLOB;
    DECLARE c_too_many_columns CONDITION FOR SQLSTATE '99006';
    -- Объявить указатель
    DECLARE c_report CURSOR WITH RETURN FOR v_cur_stmt;

    SET v_dynSQL = 'select t.tname ';
    SET v_current = p_begin;

    IF  p_end - p_begin > 254 THEN
        SIGNAL c_too_many_columns SET MESSAGE_TEXT = 'Число колонок не должно превышать 255. Уменьшите диапазон по годам';
    END IF;

    WHILE (v_current <= p_end) DO
       SET  v_dynSQL = v_dynSQL || ', SUM( CASE t.tyear WHEN ' || RTRIM(CHAR(v_current)) || ' THEN t.tcnt END ) as "' || RTRIM(CHAR(v_current)) || '"';
       SET  v_current = v_current + 1;
    END WHILE;

    SET v_dynSQL = v_dynSQL || ' FROM( SELECT d.ID as ID, COALESCE( d.NAME, ''По всем филиалам'' ) AS TNAME  , YEAR(c.reg_date) AS TYEAR,  COUNT(c.ID ) AS TCNT ' ||
    ' FROM db2admin.districts d LEFT JOIN db2admin.customers c ON d.ID = c.district_id GROUP BY GROUPING SETS( ( d.ID, d.NAME ), () ) , c.reg_date ORDER BY COALESCE (d.ID, 0) ) t GROUP BY t.ID, t.TNAME ORDER BY t.ID';
    -- Указатель оставлен открытым для программы клиента
    PREPARE v_cur_stmt FROM v_dynSQL;
    OPEN c_report;
END P1 
16 май 07, 04:25    [4141840]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с неизвестным числом колонок  [new]
ggv
Member

Откуда:
Сообщений: 1810
http://blogs.ittoolbox.com/database/technology/archives/pivot-query-12757
http://blogs.ittoolbox.com/database/technology/archives/unpivot-query-12798
17 май 07, 09:07    [4147875]     Ответить | Цитировать Сообщить модератору
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Ответить