| Subject | RE: [ib-support] Pivot table report (long example, but worth it) |
|---|---|
| Author | Jeff Jones - SHERIFFX |
| Post date | 2001-11-30T17:01:05Z |
I know this response is way after the initial question, but I have been out
of the office for several weeks and am just now getting caught up on my
mail.
There is a better way to produce the king of report you want, if you are
prepared to do a little setup work in advance. The idea comes from Joe
Celko's book "SQL for Smarties: Advanced SQL Programming", pp. 281-287.
While I can't reproduce those pages here, I can give you a quick example of
the technique Celko describes. In fact, you may just want to cut and paste
the SQL commands into your tool of choice and try them out.
First, I created a table similar to the one you described, and added some
test data:
create table sales
(
sale_year integer
, sale_month smallint
, sale_amount integer
);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 1,
100);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 2,
125);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 3,
110);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 4,
140);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 5,
125);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 6,
139);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 7,
120);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 8,
135);
insert into sales (sale_year, sale_month, sale_amount) values (2002, 1,
300);
insert into sales (sale_year, sale_month, sale_amount) values (2002, 2,
320);
insert into sales (sale_year, sale_month, sale_amount) values (2002, 3,
375);
insert into sales (sale_year, sale_month, sale_amount) values (2002, 4,
405);
commit;
Then, I created a special table called an identity matrix, and added its
data as well.
Notice that for each row, there is a "1" in the column which matches the
month number
for the row. In other words, if the month number is 1, column m_01 = 1, and
the rest
are zero.
create table month_matrix
(
m_num smallint
, m_01 smallint
, m_02 smallint
, m_03 smallint
, m_04 smallint
, m_05 smallint
, m_06 smallint
, m_07 smallint
, m_08 smallint
, m_09 smallint
, m_10 smallint
, m_11 smallint
, m_12 smallint
);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (2, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (3, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (4, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (5, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (6, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (7, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (8, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (9, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (11, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (12, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1);
commit;
Then, the following query will give you a cross tabulation of the data in
the sales table
like you described, and it's quite fast.
select sales.sale_year,
sum(sales.sale_amount * month_matrix.m_01) as m_01,
sum(sales.sale_amount * month_matrix.m_02) as m_02,
sum(sales.sale_amount * month_matrix.m_03) as m_03,
sum(sales.sale_amount * month_matrix.m_04) as m_04,
sum(sales.sale_amount * month_matrix.m_05) as m_05,
sum(sales.sale_amount * month_matrix.m_06) as m_06,
sum(sales.sale_amount * month_matrix.m_07) as m_07,
sum(sales.sale_amount * month_matrix.m_08) as m_08,
sum(sales.sale_amount * month_matrix.m_09) as m_09,
sum(sales.sale_amount * month_matrix.m_10) as m_10,
sum(sales.sale_amount * month_matrix.m_11) as m_11,
sum(sales.sale_amount * month_matrix.m_12) as m_12
from sales, month_matrix
where sales.sale_month = month_matrix.m_num
group by
sales.sale_year;
I have tried this type of query on tables with many records (over 100,000)
and it is still much faster than the one you have been using. Yes, it takes
a little time to set it up, and probably will require some playing around
with to make it fit your needs exactly, but I think it's worth it.
The best solution would be to get Joe Celko's book mentioned above and study
the passage I noted. The whole book is worth reading and trying, but this
one technique was worth the price by itself!
Jeff Jones
Maricopa County Sheriff's Office
Phoenix, Arizona, USA
Usually a lurker
-----Original Message-----
From: Artur Anjos [mailto:arsoft@...]
Sent: Tuesday, November 13, 2001 05:35 PM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Pivot table report
I use store procedures for this kind of data access. I use to have the
months also. If someone have a better solution let me know too!
Artur Anjos
of the office for several weeks and am just now getting caught up on my
mail.
There is a better way to produce the king of report you want, if you are
prepared to do a little setup work in advance. The idea comes from Joe
Celko's book "SQL for Smarties: Advanced SQL Programming", pp. 281-287.
While I can't reproduce those pages here, I can give you a quick example of
the technique Celko describes. In fact, you may just want to cut and paste
the SQL commands into your tool of choice and try them out.
First, I created a table similar to the one you described, and added some
test data:
create table sales
(
sale_year integer
, sale_month smallint
, sale_amount integer
);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 1,
100);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 2,
125);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 3,
110);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 4,
140);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 5,
125);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 6,
139);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 7,
120);
insert into sales (sale_year, sale_month, sale_amount) values (2001, 8,
135);
insert into sales (sale_year, sale_month, sale_amount) values (2002, 1,
300);
insert into sales (sale_year, sale_month, sale_amount) values (2002, 2,
320);
insert into sales (sale_year, sale_month, sale_amount) values (2002, 3,
375);
insert into sales (sale_year, sale_month, sale_amount) values (2002, 4,
405);
commit;
Then, I created a special table called an identity matrix, and added its
data as well.
Notice that for each row, there is a "1" in the column which matches the
month number
for the row. In other words, if the month number is 1, column m_01 = 1, and
the rest
are zero.
create table month_matrix
(
m_num smallint
, m_01 smallint
, m_02 smallint
, m_03 smallint
, m_04 smallint
, m_05 smallint
, m_06 smallint
, m_07 smallint
, m_08 smallint
, m_09 smallint
, m_10 smallint
, m_11 smallint
, m_12 smallint
);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (2, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (3, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (4, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (5, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (6, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (7, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (8, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (9, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (11, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0);
insert into month_matrix (m_num, m_01, m_02, m_03, m_04, m_05, m_06, m_07,
m_08, m_09, m_10, m_11, m_12)
values (12, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1);
commit;
Then, the following query will give you a cross tabulation of the data in
the sales table
like you described, and it's quite fast.
select sales.sale_year,
sum(sales.sale_amount * month_matrix.m_01) as m_01,
sum(sales.sale_amount * month_matrix.m_02) as m_02,
sum(sales.sale_amount * month_matrix.m_03) as m_03,
sum(sales.sale_amount * month_matrix.m_04) as m_04,
sum(sales.sale_amount * month_matrix.m_05) as m_05,
sum(sales.sale_amount * month_matrix.m_06) as m_06,
sum(sales.sale_amount * month_matrix.m_07) as m_07,
sum(sales.sale_amount * month_matrix.m_08) as m_08,
sum(sales.sale_amount * month_matrix.m_09) as m_09,
sum(sales.sale_amount * month_matrix.m_10) as m_10,
sum(sales.sale_amount * month_matrix.m_11) as m_11,
sum(sales.sale_amount * month_matrix.m_12) as m_12
from sales, month_matrix
where sales.sale_month = month_matrix.m_num
group by
sales.sale_year;
I have tried this type of query on tables with many records (over 100,000)
and it is still much faster than the one you have been using. Yes, it takes
a little time to set it up, and probably will require some playing around
with to make it fit your needs exactly, but I think it's worth it.
The best solution would be to get Joe Celko's book mentioned above and study
the passage I noted. The whole book is worth reading and trying, but this
one technique was worth the price by itself!
Jeff Jones
Maricopa County Sheriff's Office
Phoenix, Arizona, USA
Usually a lurker
-----Original Message-----
From: Artur Anjos [mailto:arsoft@...]
Sent: Tuesday, November 13, 2001 05:35 PM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Pivot table report
I use store procedures for this kind of data access. I use to have the
months also. If someone have a better solution let me know too!
Artur Anjos
----- Original Message -----
From: "Çagatay Tengiz" <tmd0307@...>
To: "Ib-Support" <ib-support@yahoogroups.com>
Sent: Tuesday, November 13, 2001 4:58 PM
Subject: [ib-support] Pivot table report
>
> Hi,
>
> Sippose I've a sales table which has the fields of sale_amount, sale_month
> and sale_year.
>
> So data has the following format
>
> Year Month Amount
> ---- ----- ------
> 2001 1 100
> 2001 2 100
> 2002 1 300
>
> and my problem is, showing data like this format
> 1 2
> 2001 100 100
> 2002 300
>
> I'm using
> select year,
> (select amount from mytable mt where mt.year = mt00.year) as m1,
> (select amount from mytable mt where mt.year = mt00.year) as m2,
> .
> .
> .
> from mytable
>
> but this query takes too much time...
>
> is there any better SQL solution?
>
> Cagatay Tengiz
> cagatay.tengiz@...
>
> ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
> Tengiz Engineering & Consultancy
> www.tengiz.net
>
> Phone : +90 232 388 07 01
> Fax : +90 232 373 42 63
>
> Kazim Karabekir Cad. No:47/B 35040
> Bornova - Izmir / TURKEY
> ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
>
>
>
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @... address at http://mail.yahoo.com
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/