sql - Joining a table with itself where there is a datestamp and with a secondary date variable? -

i have table - several table concatenated because [col1] takes 2 different strings , [value] takes numerical values relating [col1] string. there 2 sets of columns segments. analysis each segment , later combination of segments. datestamp available.

col1   datestamp value col4  col5  cold ret     1/10/14                     0 ret     1/11/14                     1 ret     1/11/14                     0 ent     1/10/14                     0 ent     1/11/14                     1 

finished table this:

    col1ret  col2ent  datestamp col4  col5  cold value-ret value-ent     ret       ent     1/10/14                0     ret       ent     1/11/14                1     --     -- 

what sql script this?

you can use group by aggregated case. this.

sql fiddle

query

select     max(case when col1 = 'ret' col1  else null end) col1ret,     max(case when col1 = 'ent' col1  else null end) col1ent,     datestamp,col4,cold,col5,     max(case when col1 = 'ret' value  else null end) valueret,     max(case when col1 = 'ent' value  else null end) valueent tbl group datestamp,col4,cold,col5; 

output

| col1ret | col1ent |                 datestamp |   col4 | cold |   col5 | valueret | valueent | |---------|---------|---------------------------|--------|------|--------|----------|----------| |     ret |     ent | january, 10 2014 00:00:00 | (null) |    0 | (null) |   (null) |   (null) | |     ret |  (null) | january, 11 2014 00:00:00 | (null) |    0 | (null) |   (null) |   (null) | |     ret |     ent | january, 11 2014 00:00:00 | (null) |    1 | (null) |   (null) |   (null) |