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

Comments

Post a Comment

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

i have html progress meter: <div class="col-md-3" style="margin-left: -20px;"> <div class="progress-pos active" id="progess-1"> <div class="progress-pos-inner"> login </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-2"> <div class="progress-pos-inner"> scan items </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-3"> <div class="progress-pos-inner"> confirm address </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-4"> <div class="progress-pos-in...
Read more

caching - How to check if a url path exists in the service worker cache -

i need check if particular url path exists in service worker cache. example, suppose url is: /myserviceworker/service?a=110&b=70 this url exists in cache, there many of them different values of , b. now, suppose want refresh of these urls, how can that? i want know how access key values service worker cache. if know key, plan follows: var url = new url(key); if(url.pathname === "\/myserviceworker/service") refetch key but not sure how cache key , in format is. mean, string or url? cache api has match() method returns promise resolving in response object if match or undefined if no match exists. second parameter object can specify ignoresearch not take account url parameters. the ignoresearch option supported firefox ( chrome status here ). in other hand, retrieve cache entries, can use keys() method.
Read more

Redirect to a HTTPS version using .htaccess -

i need modify .htaccess file redirect urls https version without "www". http://example.com --> https://example.com http://www.example.com --> https://example.com https://www.example.com --> https://example.com this how .htaccess file looks like: rewriteengine on rewritecond %{request_filename} !-d rewritecond %{request_filename} !-f rewriterule ^(.*)$ redirect.php?id=$1&page=$2 [qsa,l] what modifications need make .htaccess in order forward https without "www"? it depends on doing current code. looks file called redirect trying display php page seo friendly url. think might confusing people. anyway, need , force https without www, need 1 more rule above. also second rule needs two capture groups in rewriterule test string because wanting 2 different values using $1 , $2 references. rewriteengine on #rediect www non www and/or http https --- combinations. rewritecond %{http_host} !^example\.com [nc,or] rewritecond %{https} !^...
Read more