sql - Oracle 11g - Columns To Rows -

i have data coming system (format cannot changed) looking so:

row, c001,         c002, c003, c029  (columns 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 change :

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 date using sub-select row = 1. can replace (speed) (cost) in name. can't right.

  survey_query  (       select    *        tbl_data   )   select    (case when upper(sq.c001) '%fleet size%' trim(replace(upper(sq.c001), 'fleet size', ''))                 when upper(sq.c001) '%flying hours%' trim(replace(upper(sq.c001), 'flying hours', ''))            end) equipment_name          ,(select to_date(2000+dbms_lob.substr(c002,2,1)||'0101', 'yymmdd') survey_query line = 1) start_date          ,(select to_date(2000+dbms_lob.substr(c002,2,4)||'0330', 'yymmdd') survey_query line = 1) end_date           ,(case when upper(sq.c001) '%fleet size%' sq.c002 end) fleet_size           ,(case when upper(sq.c001) '%flying hours%' sq.c002 end) flying_hours    survey_query sq   line > 1  union    select   (case when upper(sq.c001) '%fleet size%' trim(replace(upper(sq.c001), 'fleet size', ''))                 when upper(sq.c001) '%flying hours%' trim(replace(upper(sq.c001), 'flying hours', ''))            end) equipment_name          ,(select to_date(2000+dbms_lob.substr(c003,2,1)||'0101', 'yymmdd') survey_query line = 1) start_date          ,(select to_date(2000+dbms_lob.substr(c003,2,4)||'0330', 'yymmdd') survey_query line = 1) end_date           ,(case when upper(sq.c001) '%fleet size%' sq.c003 end) fleet_size           ,(case when upper(sq.c001) '%flying hours%' sq.c003 end) flying_hours    survey_query sq   line > 1; 

any ideas please? there has got better way i've got 28 columns worth of data mess 27 "unions"

thanks

it's not elegant, since uses old-style pivoting, couldn't work out how via 11g pivot functionality:

with sample_data (select 1 row#, 'name' c001, 0910 c002, 1011 c003, 1112 c004 dual union                      select 2 row#, 'eqt1 (speed)' c001, 60 c002, 100 c003, 140 c004 dual union                      select 3 row#, 'eqt1 (cost)' c001, 20 c002, 30 c003, 80 c004 dual union                      select 4 row#, 'eqt2 (speed)' c001, 50 c002, 60 c003, 70 c004 dual union                      select 5 row#, 'eqt2 (cost)' c001, 30 c002, 45 c003, 56 c004 dual), -- end of mimicking table subquery called "sample_data" -- wouldn't need subquery, since have own table/query use in place -- change table name referred in res subquery below appropriate              res (select row#,                             case when c001 '%(speed)' substr(c001, 1, length(c001) - 8)                                  when c001 '%(cost)' substr(c001, 1, length(c001) - 7)                                  else c001                             end name,                             case when c001 '%(speed)' 'speed'                                  when c001 '%(cost)' 'cost'                                  else c001                             end type,                             to_date('01/04'||substr(first_value(lpad(c002, 4, 0)) on (order row#), 1, 2), 'dd/mm/rr') fy1_start_date,                             to_date('31/03'||substr(first_value(lpad(c002, 4, 0)) on (order row#), 3, 2), 'dd/mm/rr') fy1_end_date,                             to_date('01/04'||substr(first_value(lpad(c003, 4, 0)) on (order row#), 1, 2), 'dd/mm/rr') fy2_start_date,                             to_date('31/03'||substr(first_value(lpad(c003, 4, 0)) on (order row#), 3, 2), 'dd/mm/rr') fy2_end_date,                             to_date('01/04'||substr(first_value(lpad(c004, 4, 0)) on (order row#), 1, 2), 'dd/mm/rr') fy3_start_date,                             to_date('31/03'||substr(first_value(lpad(c004, 4, 0)) on (order row#), 3, 2), 'dd/mm/rr') fy3_end_date,                             c002,                             c003,                             c004                        sample_data),            dummy (select level id                        dual                      connect level <= 3 -- num fyears consider                      ) select   name,          case when d.id = 1 res.fy1_start_date               when d.id = 2 res.fy2_start_date               when d.id = 3 res.fy3_start_date          end start_date,          case when d.id = 1 res.fy1_end_date               when d.id = 2 res.fy2_end_date               when d.id = 3 res.fy3_end_date          end end_date,          max(case when d.id = 1 , res.type = 'speed' c002                   when d.id = 2 , res.type = 'speed' c003                   when d.id = 3 , res.type = 'speed' c004              end) speed,          max(case when d.id = 1 , res.type = 'cost' c002                   when d.id = 2 , res.type = 'cost' c003                   when d.id = 3 , res.type = 'cost' c004              end) cost     res          cross join dummy d    res.row# != 1 group name,          case when d.id = 1 res.fy1_start_date               when d.id = 2 res.fy2_start_date               when d.id = 3 res.fy3_start_date          end,          case when d.id = 1 res.fy1_end_date               when d.id = 2 res.fy2_end_date               when d.id = 3 res.fy3_end_date          end order 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