1

I have a road_insp table:

create table road_insp
(
    insp_id int,
    road_id int,
    insp_year int,
    condition number(10,2)
);

insert into road_insp (insp_id, road_id, insp_year, condition) values ( 1, 100, 2009, 5);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 2, 100, 2011, 3);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 3, 102, 2009, 9);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 4, 102, 2010, 7);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 5, 102, 2013, 5);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 6, 103, 2009, 10);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 7, 103, 2011, 8);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 8, 103, 2012, 6);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 9, 103, 2013, 4);

commit;

select
    insp_id,
    road_id,
    insp_year,
    condition
from 
    road_insp;

   INSP_ID    ROAD_ID  INSP_YEAR  CONDITION
---------- ---------- ---------- ----------
         1        100       2009          5
         2        100       2011          3
         3        102       2009          9
         4        102       2010          7
         5        102       2013          5
         6        103       2009         10
         7        103       2011          8
         8        103       2012          6
         9        103       2013          4

I want to compare the condition of roads, year-over-year.

I can do this by joining to sub-selects for each year:

select
    a.road_id,
    y09.condition condition_2009,
    y10.condition condition_2010,
    y11.condition condition_2011,
    y12.condition condition_2012,
    y13.condition condition_2013
from
    road_insp a
left join
    (select 
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2009
    ) y09
    on a.road_id = y09.road_id
left join
    (select 
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2010
    ) y10
    on a.road_id = y10.road_id
left join
    (select 
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2011
    ) y11
    on a.road_id = y11.road_id
left join
    (select 
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2012
    ) y12
    on a.road_id = y12.road_id
left join
    (select 
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2013
    ) y13
    on a.road_id = y13.road_id
group by
    a.road_id,
    y09.condition,
    y10.condition,
    y11.condition,
    y12.condition,
    y13.condition
order by
    road_id

   ROAD_ID CONDITION_2009 CONDITION_2010 CONDITION_2011 CONDITION_2012 CONDITION_2013
---------- -------------- -------------- -------------- -------------- --------------
       100              5                             3                              
       102              9              7                                            5
       103             10                             8              6              4

However, this method is quite cumbersome. Is there a more concise way to rotate a table for year-over-year comparison?

1
  • 2
    Take a look here - any answer by Frank Kulash is worth its salt! Also, see here. – Vérace Oct 25 '17 at 0:43
7

Yes, more concise ways of pivoting exist for your scenario.

Option 1

For instance, you could use the PIVOT operator:

SELECT
  *
FROM
  (
    SELECT
      road_id,
      insp_year,
      condition
    FROM
      road_insp
  ) derived
  PIVOT
  (
    MAX(condition)
    FOR insp_year IN
    (
      2009 AS condition_2009,
      2010 AS condition_2010,
      2011 AS condition_2011,
      2012 AS condition_2012,
      2013 AS condition_2013
    )
  )
;

PIVOT implies grouping, which is why you can see the MAX() aggregate function applied to condition rather than simply using condition.

The implicit grouping is also the reason you often need to use a derived table before applying the PIVOT operator: all columns not mentioned in the PIVOT clause actually participate in grouping, so you need to exclude those that are not supposed to be grouping criteria. For your specific example you do need a derived table, because you need to exclude insp_id.

Option 2

As an alternative to the PIVOT operator, you could try the old-school conditional aggregation method:

SELECT
  road_id,
  MAX(DECODE(insp_year, 2009, condition, NULL)) AS condition_2009,
  MAX(DECODE(insp_year, 2010, condition, NULL)) AS condition_2010,
  MAX(DECODE(insp_year, 2011, condition, NULL)) AS condition_2011,
  MAX(DECODE(insp_year, 2012, condition, NULL)) AS condition_2012,
  MAX(DECODE(insp_year, 2013, condition, NULL)) AS condition_2013
FROM
  road_insp
GROUP BY
  road_id
;

In this case there is no need to derive a table, as columns you want to group by can be explicitly specified in the GROUP BY clause.

—————————

Both methods produce the same output, identical to the one that your joining method generates, as can be seen in this demo at dbfiddle.uk.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.