Oracle 11g - Columns To Rows

Question

I have data coming into my system (format cannot be changed) looking like so:

Row, C001,         C002, C003, to C029  (Columns for FY values)
1,   Name,         0910, 1011 
2,   Eqt1 (Speed), 60,   100
3,   Eqt1 (Cost),  20,   30 
4,   Eqt2 (Speed), 50,   60
5,   Eqt2 (Cost),  30,   45

I need to change this into :

Name, Start_Date,  End_Date,    Speed, Cost
Eqt1, 01-APR-2009, 30-MAR-2010, 60,    20
Eqt1, 01-APR-2010, 30-MAR-2011, 100,   30
Eqt2, 01-APR-2009, 30-MAR-2010, 50,    30
Eqt2, 01-APR-2010, 30-MAR-2011, 60,    45

I can split the date using a sub-select where row = 1. I can replace the (Speed) (Cost) in the name. But I can't get it right.

  WITH survey_query AS  (
      SELECT    *
      FROM  tbl_data
  )
  SELECT    (CASE WHEN upper(sq.c001) LIKE '%FLEET SIZE%' THEN TRIM(REPLACE(upper(sq.c001), 'FLEET SIZE', ''))
                WHEN upper(sq.c001) LIKE '%FLYING HOURS%' THEN TRIM(REPLACE(upper(sq.c001), 'FLYING HOURS', ''))
           END) equipment_name
         ,(select TO_DATE(2000+dbms_lob.substr(c002,2,1)||'0101', 'yymmdd') FROM survey_query where line = 1) start_date
         ,(select TO_DATE(2000+dbms_lob.substr(c002,2,4)||'0330', 'yymmdd') FROM survey_query where line = 1) end_date
          ,(case when UPPER(sq.c001) like '%FLEET SIZE%' THEN sq.c002 END) fleet_size
          ,(case when UPPER(sq.c001) like '%FLYING HOURS%' THEN sq.c002 END) flying_hours
  FROM  survey_query sq
  WHERE line > 1
 UNION
   SELECT   (CASE WHEN upper(sq.c001) LIKE '%FLEET SIZE%' THEN TRIM(REPLACE(upper(sq.c001), 'FLEET SIZE', ''))
                WHEN upper(sq.c001) LIKE '%FLYING HOURS%' THEN TRIM(REPLACE(upper(sq.c001), 'FLYING HOURS', ''))
           END) equipment_name
         ,(select TO_DATE(2000+dbms_lob.substr(c003,2,1)||'0101', 'yymmdd') FROM survey_query where line = 1) start_date
         ,(select TO_DATE(2000+dbms_lob.substr(c003,2,4)||'0330', 'yymmdd') FROM survey_query where line = 1) end_date
          ,(case when UPPER(sq.c001) like '%FLEET SIZE%' THEN sq.c003 END) fleet_size
          ,(case when UPPER(sq.c001) like '%FLYING HOURS%' THEN sq.c003 END) flying_hours
  FROM  survey_query sq
  WHERE line > 1;

Any ideas please? There has got to be a better way as I've got 28 columns worth of data so will be a mess with 27 "unions"

Thanks

Answer 1

It's not very elegant, since it uses the old-style pivoting, but I couldn't work out how to do it via the 11g PIVOT functionality:

with sample_data as (select 1 row#, 'Name' c001, 0910 c002, 1011 c003, 1112 c004 from dual union all
                     select 2 row#, 'Eqt1 (Speed)' c001, 60 c002, 100 c003, 140 c004 from dual union all
                     select 3 row#, 'Eqt1 (Cost)' c001, 20 c002, 30 c003, 80 c004 from dual union all
                     select 4 row#, 'Eqt2 (Speed)' c001, 50 c002, 60 c003, 70 c004 from dual union all
                     select 5 row#, 'Eqt2 (Cost)' c001, 30 c002, 45 c003, 56 c004 from dual),
-- end of mimicking your table as a subquery called "sample_data"
-- you wouldn't need this subquery, since you would have your own table/query to use in place
-- change the table name referred to in the res subquery below as appropriate
             res as (select row#,
                            case when c001 like '%(Speed)' then substr(c001, 1, length(c001) - 8)
                                 when c001 like '%(Cost)' then substr(c001, 1, length(c001) - 7)
                                 else c001
                            end name,
                            case when c001 like '%(Speed)' then 'Speed'
                                 when c001 like '%(Cost)' then 'Cost'
                                 else c001
                            end type,
                            to_date('01/04'||substr(first_value(lpad(c002, 4, 0)) over (order by row#), 1, 2), 'dd/mm/rr') fy1_start_date,
                            to_date('31/03'||substr(first_value(lpad(c002, 4, 0)) over (order by row#), 3, 2), 'dd/mm/rr') fy1_end_date,
                            to_date('01/04'||substr(first_value(lpad(c003, 4, 0)) over (order by row#), 1, 2), 'dd/mm/rr') fy2_start_date,
                            to_date('31/03'||substr(first_value(lpad(c003, 4, 0)) over (order by row#), 3, 2), 'dd/mm/rr') fy2_end_date,
                            to_date('01/04'||substr(first_value(lpad(c004, 4, 0)) over (order by row#), 1, 2), 'dd/mm/rr') fy3_start_date,
                            to_date('31/03'||substr(first_value(lpad(c004, 4, 0)) over (order by row#), 3, 2), 'dd/mm/rr') fy3_end_date,
                            c002,
                            c003,
                            c004
                     from   sample_data),
           dummy as (select level id
                     from   dual
                     connect by level <= 3 -- num fyears to consider
                     )
select   name,
         case when d.id = 1 then res.fy1_start_date
              when d.id = 2 then res.fy2_start_date
              when d.id = 3 then res.fy3_start_date
         end start_date,
         case when d.id = 1 then res.fy1_end_date
              when d.id = 2 then res.fy2_end_date
              when d.id = 3 then res.fy3_end_date
         end end_date,
         max(case when d.id = 1 and res.type = 'Speed' then c002
                  when d.id = 2 and res.type = 'Speed' then c003
                  when d.id = 3 and res.type = 'Speed' then c004
             end) speed,
         max(case when d.id = 1 and res.type = 'Cost' then c002
                  when d.id = 2 and res.type = 'Cost' then c003
                  when d.id = 3 and res.type = 'Cost' then c004
             end) cost
from     res
         cross join dummy d
where    res.row# != 1
group by name,
         case when d.id = 1 then res.fy1_start_date
              when d.id = 2 then res.fy2_start_date
              when d.id = 3 then res.fy3_start_date
         end,
         case when d.id = 1 then res.fy1_end_date
              when d.id = 2 then res.fy2_end_date
              when d.id = 3 then res.fy3_end_date
         end
order by name, start_date;

NAME  START_DATE  END_DATE         SPEED       COST
----- ----------- ----------- ---------- ----------
Eqt1  01-APR-2009 31-MAR-2010         60         20
Eqt1  01-APR-2010 31-MAR-2011        100         30
Eqt1  01-APR-2011 31-MAR-2012        140         80
Eqt2  01-APR-2009 31-MAR-2010         50         30
Eqt2  01-APR-2010 31-MAR-2011         60         45
Eqt2  01-APR-2011 31-MAR-2012         70         56

Answer 2

Thanks for the answers, they got me thinking. Also sorry for the long wait in replying, I've been off work for 2 weeks.

I've gone with the following

WITH ss_query AS  (
      SELECT    *
      FROM  tbl_data
  )
  , rowgen as (select /*+materialize()*/level yr from dual connect by level <= 3)
  , dates as (select /*+materialize()*/to_date(substr(c001,1,2)||'0401','yymmdd') fy_start from ss_query where line = 2)
select equipment_name
, date_start
, date_end
, max(cost) cost
, max(speed) speed
from (
  select 
    case when upper(c001) like '%COST%' then substr(c001,1,instr(upper(c001),'COST')-2)
      when upper(c001) like '%SPEED%' then substr(c001,1,instr(upper(c001),'SPEED') -2)
    end equipment_name,
    add_months(fy_start,12 * (yr - 1)) date_start,
    add_months(fy_start,12 * (yr))-1 date_end,
    case when upper(c001) like '%SPEED%' then
      case when yr = 1 then c002
          when yr = 2 then c003
          when yr = 3 then c004
        end 
      end speed,
    case when upper(c001) like '%COST%' then
      case when yr = 1 then c002
          when yr = 2 then c003
          when yr = 3 then c004
        end 
      end cost
  from ss_query ,
        rowgen,
        dates
  where line > 2
)
where speed is not null or cost is not null
group by equipment_name, date_start, date_end
order by equipment_name, date_start;