title: Oracle 11g - Columns To Rows
body: <p>I have data coming into my system (format cannot be changed) looking like so:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I need to change this into :</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>  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 &gt; 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 &gt; 1;
  </code></pre>
  
  <p>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"</p>
  
  <p>Thanks</p>