сводная таблица с объединением значений

[djay]

заранее извиняюсь, если такая тема уже поднималась.

теперь по теме.

есть несколько таблиц (2,3 или больше). надо сделать "сводную" таблицу, в которой в одной ячейке могут быть данные из нескольких ячеек другой таблицы.

т.е. есть, например, столбец "расходы", куда надо записать ВСЕ значения из таблицы "расходы", относящиеся к определённому ID.

и вот тут главный вопрос - как это сделать?

 

[Aleksey]

Если надо в одной ячейке (в одном поле таблицы) собрать несколько ячеек другой таблицы, скажем перечислить через запятую значения нескольких полей другой таблицы, то надо писать скрипт на процедурном языке. Для Oracle - это PL/SQL, для Firebird - PSQL, для Access - это встроенный бейсик. То есть я о том, что нельзя одним запросом все решить.

Если надо что-то другое получить в сводной таблице, то надо более подробно сформулировать вопрос.

 

[djay]

вот мне как раз нужен пример подобного SQL-скрипта(запроса).

 

[Aleksey]

Как я уже писал, запросом сделать нельзя, по крайней мере я не могу придумать такого способа, если кто-то знает, то пусть поделится.
Я приведу пример для Oracle, для остальных БД синтаксис может сильно отличаться.

1) Допустим у нас есть две таблицы:

Сводная таблица - t_sv, в полях которой будут аккумулироваться сводные данные

create table t_sv(
p_sv_id number,
p_sv1 varchar2(100), -- в это поле будем заносить сводные данные из одной таблицы
p_sv2 varchar2(100) -- сюда из второй таблицы (ну, и дальше, если таблиц много)
);

Таблица из которой будем собирать данные (будем называть ее таблицей данных):

create table t1(
p_id number,
p_t1 varchar2(100) -- в этом поле будут данные, которые надо собрать в одну строку сводной таблицы, соответственно их id
);

2) Дальше, для примера, заполню пару строк в таблице данных:

insert into t1 values(1, 'A');
insert into t1 values(1, 'B');
insert into t1 values(1, 'C');
insert into t1 values(1, 'D');
insert into t1 values(2, 'AA');
insert into t1 values(2, 'BB');
insert into t1 values(2, 'CC');
commit;

3) Теперь создаю процедуру, которая будет склеивать множество строк из таблицы данных в одно поле сводной таблицы:

create or replace procedure test is

v_cur sys_refcursor;
v_str varchar2(100) := '';
v_rec t1%rowtype;
v_id  number		 := -1;

begin
-- Создаю курсор для построчной обработки данных из таблицы данных
open v_cur for select t.p_id, t.p_t1
from t1 t
order by t.p_id;

-- цикл по всем записям таблицы данных
loop

-- извлекаю одну строку в переменную-запись
fetch v_cur into v_rec;
exit when v_cur%notfound;

-- есть ли в сводной таблице запись с p_id, который мы только что извлекли из таблицы данных
-- если ее нет, то получим 0 и избежим исключения NO_DATA_FOUND
select count(1) into v_id
from t_sv t
where t.p_sv_id=v_rec.p_id;

-- либо создаем новую запись в сводной таблице,
-- либо добавляем в уже существующую значение, извлеченное из таблицы данных
if v_id=0 then
insert into t_sv (p_sv_id, p_sv1) values (v_rec.p_id, v_rec.p_t1);
else
select t.p_sv1 into v_str
from t_sv t
where t.p_sv_id=v_rec.p_id;

v_str := v_str || ', ' || v_rec.p_t1;
update t_sv t
set t.p_sv1=v_str
where t.p_sv_id=v_rec.p_id;
end if;

end loop;

-- закрываю курсор
close v_cur;

end test;

4) Из жгучего любопытства запускаю процедуру:

begin
test;
end; 
/

И смотрю что получилось:

select * from t_sv;

P_SV_ID	P_SV1
1			 A, B, C, D
2			 AA, BB, CC

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

 

[djay]

структура базы кривовата - не спорю. но такая фигня (читать - процедура) нужна только в одном случае.

спасибо за пример, буду тестить :blink:

а вот базу бы сам с удовольствием переделал на другую структуру... но... суммарное число записей в таблицах > 500k. как бы это.. самое... задолбаюсь :blink:

 

[Aleksey]

Руками же не будешь данные носить :blink: , все потихоньку скриптами да селектами...
Главное - не спешить.

 

[djay]

да я-то уже не спешу. мне б пока оболочку для этой базы сделать, чтобы отстали.
и можно потихоньку начинать переделывать...
просто база-то - производственная. если сча всё брошу и начну переделывать, то... не вариант, короч :mellow:

а база эта досталась мне "по наследству" от первого программера в нашей конторе... чем он думал, когда базу проектировал - не знаю.
но итог плачевный вышел когда я первый раз структуру базы увидел, то мне даж страшно стало )))

и неудивительно, что Delphi-приложение, написанное тем же программером для этой базы, грузится по минуте даже на моём 2-ядерном ноуте с 2 гигами мозга, да к тому же стабильно вылетает раз 6-7 за день...