Transpose a Table Oracle 10

Question

I've read some question but is not clear for me, and i can't use a pivot

I have the next table:

ID AREA CAR
1   A1   A
1   A2   B
1   A3   C
2   A1   D
2   A2   E
3   A2   F
3   A3   G

And I want some like

ID  AREA1  CAR1  AREA2  CAR2  AREA3  CAR3
1     A1     A    A2     B      A3    C         
2     A1     D    A2     D     null  null                   
3    null   null  A2     F      A3    G                    

The number of areas is fixed, just A1,A2,A3.

I've tried with

SELECT     id, area1,car1,area2,car2
FROM       (  SELECT        id,
                        case when AREA='A1' then AREA else NULL end area1,
                        case when AREA='A1' then CAR else NULL end car1,   
                        case when AREA='A2' then AREA else NULL end area2,
                        case when AREA='A2' then CAR else NULL end car2,
                        case when AREA='A3' then AREA else NULL end area3,
                        case when AREA='A3' then CAR else NULL end car3
          FROM        TABLA
          GROUP BY    id );

But I get:

"not a GROUP BY expression"

What can I do to have a correct GROUP BY expression and transpose my table correctly? Is there a better solution to do it?

Thanks in advance

Answer 1

Unfortunately, there is no PIVOT function on Oracle 10. Your query was close but there is no need to wrap that inside of another query:

select id,
  min(case when area = 'A1' then area end) area1,
  min(case when area = 'A1' then car end) car1,
  min(case when area = 'A2' then area end) area2,
  min(case when area = 'A2' then car end) car2,
  min(case when area = 'A3' then area end) area3,
  min(case when area = 'A3' then car end) car3
from yourTable
group by id

Answer 2

When you do a GROUP BY, you need to aggregate any columns that you are not grouping by. You probably want something like

SELECT     id, area1,car1,area2,car2
FROM       (  SELECT        id,
                        max( case when AREA='A1' then AREA else NULL end) area1,
                        max( case when AREA='A1' then CAR else NULL end) car1,   
                        max( case when AREA='A2' then AREA else NULL end) area2,
                        max( case when AREA='A2' then CAR else NULL end) car2,
                        max( case when AREA='A3' then AREA else NULL end) area3,
                        max( case when AREA='A3' then CAR else NULL end) car3
          FROM        TABLA
          GROUP BY    id );

You could also move the aggregation and the GROUP BY to the outer query

SELECT     id, max(area1),max(car1),max(area2),max(car2)
FROM       (  SELECT        id,
                        case when AREA='A1' then AREA else NULL end area1,
                        case when AREA='A1' then CAR else NULL end car1,   
                        case when AREA='A2' then AREA else NULL end area2,
                        case when AREA='A2' then CAR else NULL end car2,
                        case when AREA='A3' then AREA else NULL end area3,
                        case when AREA='A3' then CAR else NULL end car3
          FROM        TABLA)
GROUP BY    id;