Data format and display

Hello Steve,

El 20/01/2005 5:20 PM, Steve Crawford en su mensaje escribio:

select
zert_title as TITULO,
sum(case when epr_mes=200309 then epr_valor else 0 end) as "200309",
sum(case when epr_mes=200310 then epr_valor else 0 end) as "200310",
sum(case when epr_mes=200311 then epr_valor else 0 end) as "200311",
sum(case when epr_mes=200312 then epr_valor else 0 end) as "200312"
group by 1;

That works ok, but start and end month are variables, user can choose them.

Thanks,

--
Sinceramente,
Josu� Maldonado.

"Los estupidos adolecentes son los que se reunen por las noches a
consumir drogas y a planear los ilicitos."

I have a table that contains this raw data:

epr_procode | epr_tipo | epr_mes | epr_valor | zert_title
-------------+----------+---------+-----------+--------------------
-- 00C188 | VTA | 200309 | 2116.0000 | Venta
00C188 | CTO | 200309 | 1600.0700 | Costo
00C188 | VTA | 200311 | 3450.0000 | Venta
00C188 | CTO | 200311 | 2687.4200 | Costo

I need to display it this way:

TITULO |200309 |200310 |200311 |200312
-------------+----------+---------+-----------+--------------------
-- Venta |2116.0000 |0.0000 |3450.0000 |0.0000
Costo |1600.0700 |0.0000 |2687.4200 |0.0000

Notice I must display the missing 200310 and 200312 empty data
since users retrieves the info in four months based data set, of
course that data does not exist since there were no trans in those
months. Any idea or suggestion to get it done in Postgresql.

Here's one way:

select
zert_title as TITULO,
sum(case when epr_mes=200309 then epr_valor else 0 end) as "200309",
sum(case when epr_mes=200310 then epr_valor else 0 end) as "200310",
sum(case when epr_mes=200311 then epr_valor else 0 end) as "200311",
sum(case when epr_mes=200312 then epr_valor else 0 end) as "200312"
group by 1;

Cheers,
Steve

=?ISO-8859-1?Q?Josu=E9_Maldonado?= <josue@lamundial.hn> writes:

I have a table that contains this raw data:

epr_procode | epr_tipo | epr_mes | epr_valor | zert_title
-------------+----------+---------+-----------+----------------------
00C188 | VTA | 200309 | 2116.0000 | Venta
00C188 | CTO | 200309 | 1600.0700 | Costo
00C188 | VTA | 200311 | 3450.0000 | Venta
00C188 | CTO | 200311 | 2687.4200 | Costo

I need to display it this way:

TITULO |200309 |200310 |200311 |200312
-------------+----------+---------+-----------+----------------------
Venta |2116.0000 |0.0000 |3450.0000 |0.0000
Costo |1600.0700 |0.0000 |2687.4200 |0.0000

I think the "crosstab" functions in contrib/tablefunc/ might help you.

regards, tom lane