sqlite3 - Transpose rows to columns in SQLite -


i have data this:

enter image description here

i trying transform (using sqlite). in desired result, within each id, each start should on same row chronologically closest end. if id has start no end (like id=4), corresponding end, empty (as shown below).

enter image description here

i have tried this

select      id,     max( case when start_end = "start" date end) start,     max(case when start_end = "end"   date end ) end df group id

but result this, wrong because id=5 have 1 row, when should have two:

  id      start        end 1  2 1994-05-01 1996-11-04 2  4 1979-07-18       <na> 3  5 2010-10-01 2012-10-06

any appreciated

    create table mytable(    id        integer  not null primary key    ,start_end varchar(5) not null   ,date      date  not null ); insert mytable(id,start_end,date) values (2,'start','1994-05-01'); insert mytable(id,start_end,date) values (2,'end','1996-11-04'); insert mytable(id,start_end,date) values (4,'start','1979-07-18'); insert mytable(id,start_end,date) values (5,'start','2005-02-01'); insert mytable(id,start_end,date) values (5,'end','2009-09-17'); insert mytable(id,start_end,date) values (5,'start','2010-10-01'); insert mytable(id,start_end,date) values (5,'end','2012-10-06'); 

select   s.id        id,   s.date      'start',   min(e.date) 'end' -- earliest end date "same id&start"   -- start dates   (select id, date    intable    start_end='start'    ) s left join -- keep start-only lines   -- end dates   (select id, date    intable    start_end='end'    ) e on      s.id = e.id   , s.date < e.date -- not group s.id, s.date -- "same id&start" order s.id, s.date; -- ensure sequence
  • left join (to keep start-only line id "4") 2 on-the-fly tables, start dates , end dates.
  • take minimal end date higher start date (same id, using min()and group by.
  • order id, start date.

i tested on test table similar dump, has no "not null" , no "primary key". guess test table irrelevant; otherwise explain effect, please.

note:
internally 3 pairs of dates id 5 (those match end>start) found, forwarded lowest end (min(end)) each of 2 different combinations of id , start group id, start. line end>start end not being minimum therefor not returned. makes 2 lines start/end pairs desired.

output (with .headers on): 

id|start|end 2|1994-05-01|1996-11-04   4|1979-07-18|   5|2005-02-01|2009-09-17   5|2010-10-01|2012-10-06

update: incorporate helpful comments @matbailie.


Comments

Post a Comment

Popular posts from this blog

inversion of control - Autofac named registration constructor injection -

does autofac support specifying registration name in components' constructors? example: ninject's namedattribute . you need use autofac.extras.attributed package on top achieve this so let's have 1 interface , 2 classes: public interface ihello { string sayhello(); } public class englishhello : ihello { public string sayhello() { return "hello"; } } public class frenchhello : ihello { public string sayhello() { return "bonjour"; } } then have consumer class, in want select instance injected: public class helloconsumer { private readonly ihello helloservice; public helloconsumer([withkey("en")] ihello helloservice) { if (helloservice == null) { throw new argumentnullexception("helloservice"); } this.helloservice = helloservice; } public string sayhello() { return this.helloservice.sayhello()...
Read more

verilog - Systemverilog dynamic casting issues -

i've code snippet following in testbench function void write_to_port( my_data_type_base data ); my_data_type_extended data_ext; if(!$cast(data_ext, data)); `uvm_error(get_type_name(), "failed cast"); `uvm_info(get_name(), $psprintf("data_ext :\n%s", data_ext.sprint()), uvm_medium) // write data_ext out port.... endfunction when run it, i'm getting uvm_error "failed cast." i'm not quire sure why $cast not returning 1. can see, i'm printing out extended class data item after casting uvm_info. can see it's being cast properly. if don't use $cast if condition, don't runtime error. isn't coding practice use if dynamic cast check if $cast returning 1 ? what might reason behind cast not returning 1 in above case? i think semicolon on line 'if' not belong? i think consumes if statement, , uvm_error executes regardless of how if evaluates: if(!$cast(data_ext, data)); <- no semico...
Read more

ios - Change Storyboard View using Seague -

i have following code checks connection server, , depending on want change views if no connection found. nsstring *connect = [nsstring stringwithcontentsofurl:[nsurl urlwithstring:@"http://myserver.com"] encoding:nsutf8stringencoding error:nil]; if (connect == null) { [self performseguewithidentifier:@"network_failure" sender:self]; } else { // other code here } my problem not change views. have added storyboard segue between 2 views identifier of network_failure . not have navigation controller or that, problem? thanks help! no not problem. first of all, in objectivec exists nil object oriented null . must use that. so: if(connect == nil) or better: if(!connect) so due fact nil not same of null , probably code does't enter in if scope . you have been able discover breakpoint. anyway: be sure set trigger segue (line) 2 view controller , , name network_failure (that not best name. should instead view1toview2 ) as, m...
Read more