Thread: Data format and display

Hello list,

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.

Thanks in advance



--
Sinceramente,
Josué Maldonado.

"Las palabras de aliento después de la censura son como el sol tras el
aguacero."

> 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

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."

=?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