2

I'm having a lot of difficulties in manipulating information in order to achieve the desired result. Through a query I join with the following result:

   Area     |  ID_AT  |     AT    | TYPE
-----------------------------------------
Informatica |    1    |  Sistemaa |  E
Informatica |    3    | engraçado |  E
Informatica |    3    | engraçado |  I
Gestão      |    2    |   aaaaa   |  I

query:

select a.Area, c.ID_AT, c.AT, dest.type 
from AREA_AT a
left join AT_C c on a.id_AREA_AT = c.id_AREA_AT
left join dest_atv d on d.id_AT = c.id_at and d.id_uo = c.id_uo
left join CLIE dest on d.id_CLIE = dest.id_CLIE
where id.uo = 1222 
order by a.id_AREA_AT, c.id_at, dest.type

But what I want is to create a table in php as follows:

   Area     |  ID_AT  |     AT    | E | I
-------------------------------------------
Informatica |    1    |  Sistemaa | X |
Informatica |    3    | engraçado | X | X
Gestão      |    2    |   aaaaa   |   | X

In short, what I intend to do here is to show only one table ID ativ, showing that may exist or not type I or E depending on what comes from the query.

Do I have to modify the query to facilitate the work myself? Or will I have to create a fairly complicated algorithm in php to perform this function? Ando here turns hours and still could not find a solution that will can help me?

UZUMAKI
  • 43
  • 1
  • 7

1 Answers1

0

It sounds like you need to do a pivot query. Something like

select a.Area, 
       c.ID_AT, 
       c.AT, 
       max( case when dest.type = 'E' then 'X' else null end) E,
       max( case when dest.type = 'I' then 'X' else null end) I
  from AREA_AT a
       left join AT_C c on a.id_AREA_AT = c.id_AREA_AT
       left join dest_atv d on d.id_AT = c.id_at and d.id_uo = c.id_uo
       left join CLIE dest on d.id_CLIE = dest.id_CLIE
 where id.uo = 1222 
 group by a.area, c.id_at, c.at
 order by a.area, c.id_at

Note that you won't be able to ORDER BY the a.id_area_at column if you are not selecting (and grouping by) that column in this formulation. I changed that to sort by a.area instead.

Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Perfect :D I did not know it was possible to obtain this result for a query. Thank you very much. – UZUMAKI Dec 03 '12 at 10:48
  • Another question enjoying your answer. How is it possible to get the dest.id_CLIE knowing that E and I are both X? – UZUMAKI Dec 03 '12 at 12:48
  • @UZUMAKI - Sorry, I'm not sure that I understand the question. Are you asking how to retrieve just the row with an `ID_AT` value of 3 from the results of the pivot query? – Justin Cave Dec 03 '12 at 12:52
  • No. I'm asking if it is possible to obtain `dest.id_CLIE` (id customers) corresponding to the result of the `id_At` value of 3 – UZUMAKI Dec 03 '12 at 12:59
  • @UZUMAKI - Is the `dest.id_clie` value identical in both of the rows that are getting combined to produce the aggregated output. – Justin Cave Dec 03 '12 at 13:15
  • @UZUMAKI - So, then, you want to pivot the results so that you get one row per `id_at` and then return two rows with two different `id_cle` values? – Justin Cave Dec 03 '12 at 14:57
  • Right. If I understand the operation of the pivot query, I think it will not be possible. But is possible through another query? Or the best solution is to try to manipulate the results through php. – UZUMAKI Dec 03 '12 at 15:03
  • Already solved with php. Thank you for any attention given to my problem – UZUMAKI Dec 03 '12 at 16:59