Трансформация таблицы

← →
VitalikS (2006-08-03 06:40) [0]

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

← →
Сергей М. © (2006-08-03 08:41) [1]

Нельзя.
Но можно получив НД любым подходящим запросом трансформировать этот НД уже на стороне клиента.

← →
Сергей М. © (2006-08-03 08:43) [2]

Впрочем можно поизвращаться в хранимой процедуре, но овчинка выделки не стоит.

← →
StriderMan © (2006-08-03 08:54) [3]

> VitalikS (03.08.06 06:40)
> Можно ли как-нибудь SQL запросом трансформировать таблицу
> (т.е. чтобы строки стали столбцами)?

это наверное называется транспонировать. Как матрицу :). Экзотическая, надо сказать, задача. А зачем это, если не секрет?

← →
sniknik © (2006-08-03 09:03) [4]

> Экзотическая, надо сказать, задача.
не совсем, "шахматка" в бугалтерском деле повседневная вещь... правда там обычно не просто таблицу разворачивают, а итог запроса чтонибудь считающего суммы по месяцам например, так им удобнее говорят.

← →
Danilka © (2006-08-03 09:16) [5]

я чаще всего встречался со случаями, когда и количество строк и количество столбцов динамическое. то-есть, как ни верти, все одно не угадаешь. :)
та-же шахматка, или какие-то показатели в строках, а в столбцах месяца.
обычно, делал запрос, который возвращает набор данных из 3-х полей: строка, столбец и значение показателя.
например: наименование показателя, название месяца и сумма.
а на клиенте уже формировал либо в экселе, либо в html отчет.

← →
PEAKTOP © (2006-08-03 09:42) [6]

Можно сделать процедуру с переменным числом столбцов через EXECUTE STATEMENT, благо он разрешает не только select. Для этого
1) выделить период во времени, в течение которого будут актуальны данные. Например, для той же шахматки - один месяц, квартал. Сформулировать правило имен хранимых процедур, для того, чтобы для одного периода времени была только одно процедура (ну, не будем же мы их "плодить" до бесконечности :))), например, create procedure proc$shahmatka_200606, proc$shahmatka_200607, proc$shahmatka_200608 и т.д.
2) создать процедуру PROC_CHECK(q_date timestamp), которая будет проверять, существует ли процедура для периода, в котором находится дата, формировать скрипт create or alter procedure для этого отчетно периода, в котром будет переменное число столбцов в зависимости от результатов кросс-таб запроса. Ну, и соответственно выполнять запрос, создающий/изменяющий хранимую процедуру.
3) на клиенте запускаем
if ExecSQL("execute procedure proce_check("""+FormatDateTime("dd.mm.yyyy", Now)+""") ") then
begin
IBQuery.SQL.Text := "select * from proc$shahmatka_"+FormatDateTime("yyyymm", Now);
if not IBQuery.Active then
IBQuery.Open;
......
end

Преимущества этого способа вижу в том, что позволяет "запихнуть" кросс-табы в TDBChart, особенно, когда требуется сравнительные динамики продаж по филиалам, а, скажем, в январе одного из филиалов и в помине еще не было.
Недостатки вижу в том, что все-таки эта гадость "плодит" в базе данных процедуры, что в общем-то с точки зрения теории не очень хорошо.

← →
Desdechado © (2006-08-03 10:49) [7]

PEAKTOP © (03.08.06 09:42) [6]
Главная "гадость" в том, что в случае многопользовательской работы создание на лету процедур - плохая практика, чреватая конфликтами и сбоями.

Разворот данных лучше делать на клиенте.
SQL требует явного указания возвращаемых полей (т.е. их число известно), а в случае разворота число строк произвольное, поэтому SQL таких возможностей не предоставляет.

← →
Dok (2006-08-03 11:09) [8]

> Разворот данных лучше делать на клиенте.

Если автору надо для отчета - это самый правильный совет.

← →
Sergey13 © (2006-08-03 11:13) [9]

> [8] Dok (03.08.06 11:09)

А НЕ для отчета? ОТОБРАЖЕНИЕМ данных, ИМХО, по любому должен клиент заниматься.

← →
atruhin © (2006-08-03 11:23) [10]

В Firebird 2 можно, с помощью EXECUTE BLOCK. Тем более финальный релиз должен быть в ближайшие месяц, два.
Но я бы предпочел сделать это на клиенте. Как вариант для большей гибкости неплох
> [5] Danilka © (03.08.06 09:16)
, или например возврат строк, где значения ячеек через разделитель.

> Преимущества этого способа вижу в том, что позволяет "запихнуть"
> кросс-табы в TDBChart

Хороши преимущества! Если же тебе нужен потомок датасета, то проще на клиенте заполнить любой клиентский датасет.

← →
PEAKTOP © (2006-08-03 12:36) [11]

> Desdechado © (03.08.06 10:49) [7]
> Главная "гадость" в том, что в случае многопользовательской работы создание на лету процедур - плохая практика, чреватая конфликтами и сбоями.
>

Согласен. Может и база "с катушек съехать". Но если какой-то отчет смотрит только директор или глав-бух раз в месяц, то можно рискнуть...

> atruhin © (03.08.06 11:23) [10]
>
> > Преимущества этого способа вижу в том, что позволяет "запихнуть"
> > кросс-табы в TDBChart
>
> Хороши преимущества! Если же тебе нужен потомок датасета, то проще на клиенте заполнить любой клиентский датасет.
>

Зашибись, а я бы не додумался. Только вот как запихнуть потомок от TDataSet, который "развернет" кросс-таб, в скомпиленный кем-то 300 лет назад exe-шник, который нужно сопровождать. Исходников нет, автор скорее всего уже где-то в Германии.

← →
atruhin © (2006-08-03 12:57) [12]

А как скомпилированный 300 лет назад exe-шник, будет искать, создавать и вызывать твои непонятные процедуры.
Мы вроде говорим о написании программы, а не о изменении exe файла!
Если на то пошло, то в начале поста я предложил более коррекное решение, возможно на 1-2 секунды медленнее (время компиляции запроса), но для вызова раз в месяц самое то.

← →
Dok (2006-08-03 13:54) [13]

> А НЕ для отчета? ОТОБРАЖЕНИЕМ данных, ИМХО, по любому должен
> клиент заниматься

Я и не спорю что отображением данных должен клиент заниматься, я говорю что для Отчета - клиент. Если допустим афтор хотел как-то для расчетов заюзать(OLAP), то такие весчи однозначно(!) должен делать сервер.

← →
Desdechado © (2006-08-03 13:56) [14]

> OLAP), то такие весчи однозначно(!) должен делать сервер.
Согласно новым веяниям - сервер приложений, а не сервер БД.

← →
Dok (2006-08-03 13:58) [15]

> Согласно новым веяниям - сервер приложений, а не сервер
> БД.

С чего-бы это серверу приложения заниматься расчетом OLAP? Только сервер БД !

← →
Sergey13 © (2006-08-03 14:05) [16]

Насколько я понимаю (я ОЛАП-ом не занимался, просто читал/слышал) для ОЛАП-а выделяется свой сервер БД.

← →
Desdechado © (2006-08-03 14:09) [17]

Dok (03.08.06 13:58) [15]
Спорить не буду.
Обычно трактуется так: задача СУБД - хранить. Задача сервера приложений - обрабатывать. OLAP - это массовая обработка, поэтому грузить ей сервер БД негуманно.

← →
Dok (2006-08-03 14:11) [18]

> ОЛАП-а выделяется свой сервер БД.

вот именно, не сервер приложения - а сервер СУБД.

← →
Sergey13 © (2006-08-03 14:17) [19]

> [18] Dok (03.08.06 14:11)

Да, только он имеет мало общего с "боевой" БД.

← →
Dok (2006-08-03 14:21) [20]

> Да, только он имеет мало общего с "боевой" БД.

и?
кто расчитывает?

← →
Sergey13 © (2006-08-03 14:27) [21]

> [20] Dok (03.08.06 14:21)

ОЛАП и расчитывает, но, насколько я знаю, данные в нем лежат не так, как в "боевом" сервере, а "несколько" иначе - более денормализовано для скорости расчетов. Так что это уже получается некий симбиоз сервера БД и приложений.

← →
Sergey13 © (2006-08-03 14:28) [22]

Кстати, не думаю, что ОЛАП - это то чем занимается автор топика.

← →
Dok (2006-08-03 14:54) [23]

> ОЛАП и расчитывает, но, насколько я знаю, данные в нем лежат
> не так, как в "боевом" сервере, а "несколько" иначе - более
> денормализовано для скорости расчетов. Так что это уже получается
> некий симбиоз сервера БД и приложений.
>

По разному есть и расчитанные данные лежат. есть и динамические данные, но никак этим не приложение занимается,а ХП и запросы.

> Кстати, не думаю, что ОЛАП - это то чем занимается автор
> топика.

Мне тоже так кажется :) Но думаю после этой дискуссии у афтора прояснится что он хотел.

← →
Desdechado © (2006-08-03 15:39) [24]

> но никак этим не приложение занимается
Приложение и сервер приложений для тебя разницу имеют?
Если нет, то с тобой все ясно. Если да, тогда зачем жонглируешь терминами?

← →
Dok (2006-08-03 15:58) [25]

> Приложение и сервер приложений для тебя разницу имеют?

так же как килограмм и метр, а что?

> Если да, тогда зачем жонглируешь терминами?

а для тебя есть разница сервер азы данных и сервер приложения?

← →
Desdechado © (2006-08-03 16:05) [26]

Dok (03.08.06 15:58) [25]
Похоже, ты из Одессы. Поганая привычка - отвечать вопросом на вопрос.

> а для тебя есть разница сервер азы данных и сервер приложения?
Читаешь по диагонали? С тобой все ясно. Я все сказал, еще в [17].
Повторять смысла не вижу.

← →
VitalikS (2006-08-04 04:34) [27]

Уфффф...
Спасибо всем кто отвечал

> то наверное называется транспонировать. Как матрицу :).
> Экзотическая, надо сказать, задача. А зачем это, если не
> секрет?

Да правильно транспонировать, ошибся я.
Дело в том что данные о платежах храняться в таблице построчно (т.е. одна квитанция занимает от 4х строк: фамилия, л/с, сумма ....) у каждого элемента свой номер фамилия -16, сумма-1, л/с - 32 ........ на каждую организацию кол-во элементов разное поэтому ради универсальности приложения сделано такое хранение данных. Так вот мне нужно по заранее известной схеме (для каждой организации своя) перевернуть эти данные в нормальную форму (т.е. чтобы элементы были столбцами) и чтобы получился в итоге нормальный понятный список.

← →
Danilka © (2006-08-04 08:00) [28]

[27] VitalikS (04.08.06 04:34)
чуть кофием не подавился.
просто интересно, это по-мотивам статьи Анатолия Тенцера "БД – хранилище объектов", или какая-то собственная выдумка?

> [27] VitalikS (04.08.06 04:34)
ИМХО.
Удаление гланд электродрелью через .... Вобщем - я тащусь от такой "универсальности"
> по заранее известной схеме (для каждой организации своя)

← →
VitalikS (2006-08-04 08:36) [30]

> просто интересно, это по-мотивам статьи Анатолия Тенцера
> "БД – хранилище объектов", или какая-то собственная выдумка?
>

Не совсем своя Москва придумала. Мы подстраиваемся. Да и универсально вроде, можно принимать платежи от разных организаций с их потребностями.
> Удаление гланд электродрелью через .... Вобщем - я тащусь
> от такой "универсальности"

Ничего не поделаешь, а как бы ты организовал такое?
> по заранее известной схеме (для каждой организации своя)

Под схемой понимается различные элементы в квитанции.

> [30] VitalikS (04.08.06 08:36)
> Ничего не поделаешь, а как бы ты организовал такое?
> Под схемой понимается различные элементы в квитанции.
Я бы сделал таблицу плательщиков/получателей и таблицу платежей, в которой были бы все возможные реквизиты. Если реквизит не заполнен, то и не печатается/не обрабатывается.

Если выпихнуть исходные данные в Excel и наложить сводную таблицу?

[30] VitalikS (04.08.06 08:36)
Основная поблема, на мой взгляд, не в скорости регистрации документов. И не в размерах БД.
А в скорости формирования различных отчетов.
Твой вариант, предложеный Москвой, будет наиболее тормозной, из всех возможных.
Можно было-бы сделать так, как в [31], или несколько таблиц: одна с обязательными реквизитами от всех, другая - по твоему варианту, с тремя полями: ид из первой таблицы, ид реквизита и значение реквизита, для редко используемых кем-то, экзотических реквизитов.
Если переделать структуру базы возможности нет, то только два варианта - тащить на клиента как есть, и уже там строить отчет, либо запросом разворачиваьт каждый реквизит, например, как из упоминаемой мною статьи Тенцера:

SELECT O.Id, S1.Value AS Family, S2.Value AS FirstName, S3.Value AS LastName, H.ItemDate AS BirthDate FROM Objects O INNER JOIN ObjType OT ON OT.Id = O.TypeId LEFT JOIN Strings S1 ON O.Id = S1.ObjectId LEFT JOIN StrDesc SD1 ON S1.TypeId = SD1.Id LEFT JOIN Strings S2 ON O.Id = S2.ObjectId LEFT JOIN StrDesc SD2 ON S2.TypeId = SD2.Id LEFT JOIN Strings S3 ON O.Id = S3.ObjectId LEFT JOIN StrDesc SD3 ON S3.TypeId = SD3.Id LEFT JOIN History H ON O.Id = H.ObjectId LEFT JOIN Status S ON H.TypeId = S.Id WHERE OT.Code = ‘PEOPLE’ AND SD1.Code = ‘FAMILY’ AND SD2.Code = ‘FIRSTNAME’ AND SD3.Code = ‘LASTNAME’ AND S.Code = ‘BIRTHDATE’

← →
Виталий Панасенко (2006-08-04 10:44) [34]

NXDBGRID не поможет Отцу русской демократии ?

← →
Виталий Панасенко (2006-08-04 10:45) [35]

Если, конечно, вся проблема в визуализации для клиента...

> Не совсем своя Москва придумала.

Правильно придумала

> в которой были бы все возможные реквизиты

И если появится еще один придется альтерить табличку. Некузяво.

Все имхо.

> [36] ANB © (04.08.06 11:57)

Ну давай все в одну табличку свалим и будем по кодам выбирать и товар и людей и основные средства. 8-) ИМХО тоже.

Кроме того
>И если появится еще один придется альтерить табличку. Некузяво.
Это за отдельную плату - сколько угодно с превеликим удовольствием. 8-)

> Ну давай все в одну табличку свалим и будем по кодам выбирать
> и товар и людей и основные средства.

Кстати, прекрасная идея ! В некоторых случаях вполне применимо. Во всяком случае в АИС Налог физики и юрики лежат в одной таблице FN212.

> [38] ANB © (04.08.06 15:56)
> Кстати, прекрасная идея ! В некоторых случаях вполне применимо.
> Во всяком случае в АИС Налог физики и юрики лежат в одной
> таблице FN212.

Это что! У меня во всех программах в одной таблице лежат даже мужчины и женщины! О как! И не размножаются. 8-)))))))))

> Sergey13 © (04.08.06 15:58) [39]

Во всяком случае идея поворачивать таблицу набок при хранении переменного числа атрибутов весьма не нова и давно используется.

Трансформация таблицы Найти похожие ветки