-2

I have 12 rows for year and name, but I want 1 row for year and name with 12 register in (1 or null). 

select id_agente, nombre, ene, feb, mar, abr, may, jun, jul, ago, sep, oct, nov, dic 
from (
   (select 
       a.id_agente id_agente,  
       a.NOMBRE nombre,
       case
           when to_char (to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '01' then 1 end ene,
       case
           when to_char (to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '02' then 1 end feb,
  ..................     
  )
  group by nombre, id_agente, ene, feb, mar, abr, may, jun, jul, ago, sep, oct, nov, dic 
  order by nombre, id_agente, ene, feb, mar, abr, may, jun, jul, ago, sep, oct, nov, dic;

I hope you help me, thank you very much!

Eduardo S.
  • 1
  • 1

2 Answers2

1

There are several solutions to this common problem.

  1. If You are using Oracle 11g or newer, You can use PIVOT. It is quite tricky to use.
  2. You can write PL/SQL function that will return 'table' with all the data You want in any format You want.
  3. You can Select one row with all months numbers from dual, then join them with Your query (preferably using WITH statement) 12 times (one for each month).
  4. You can also realize that You do not need it in one row, because probably - You don't. :) maybe You can modify result where You get it from database, and then create one column for each month. Or maybe You can display it as a table and so on...

I think there are some other solutions as well, but I suggest You go for number 4. I remember having similar problem quite often with SQL in the beginning, but they just fade away now.

I suppose You are approaching Your problem from wrong angle.

T.Z.
  • 2,092
  • 1
  • 24
  • 39
0

One approach, although verbose, is to define each aggregate column with a case statement as the argument to summarize. Then the "group by" and "order by" clauses only need to be given "id_agente" and "nombre". I've used this approach several times with Oracle data.

The below query is untested, and it makes some assumptions, but it should at least be a good start.

select a.id_agente id_agente,  
       a.NOMBRE nombre,
       sum(case when to_char(to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '01' then 1 else 0 end) ene,
       sum(case when to_char(to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '02' then 1 else 0 end) feb,
       sum(case when to_char(to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '03' then 1 else 0 end) mar,
       sum(case when to_char(to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '04' then 1 else 0 end) abr,
       sum(case when to_char(to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '05' then 1 else 0 end) may,
       sum(case when to_char(to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '06' then 1 else 0 end) jun,
       sum(case when to_char(to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '07' then 1 else 0 end) jul,
       sum(case when to_char(to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '08' then 1 else 0 end) ago,
       sum(case when to_char(to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '09' then 1 else 0 end) sep,
       sum(case when to_char(to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '10' then 1 else 0 end) oct,
       sum(case when to_char(to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '11' then 1 else 0 end) nov,
       sum(case when to_char(to_date(dec.FECHA_RECOGIDA_ORIGEN,'dd/mm/yyyy'),'mm') = '12' then 1 else 0 end) dic
  from dec
group by id_agente,
         nombre
order by id_agente,
         nombre;

However, using a pivot query, like Dark Anavger mentioned, would likely be the most formal approach if your output format needs to be a single row with twelve summary columns.

Good luck!

Bryce Caine
  • 91
  • 3