CODEWITHSUNDEEP
sqlsql-servert-sqlunpivot
Nico
Nico

Reputation: 1197

unpivot() with additional information

i have some data like:

t   starttime                 endtime

1   2012-11-13 07:00:00.000   2012-11-13 09:00:00.000
1   2012-11-13 09:00:00.000   2012-11-13 09:15:00.000
2   2012-11-13 09:00:00.000   2012-11-13 16:00:00.000
1   2012-11-13 09:15:00.000   2012-11-13 12:00:00.000
1   2012-11-13 12:30:00.000   2012-11-13 15:00:00.000
1   2012-11-14 07:00:00.000   2012-11-14 09:00:00.000
1   2012-11-14 09:00:00.000   2012-11-14 09:15:00.000
1   2012-11-14 09:15:00.000   2012-11-14 12:00:00.000
1   2012-11-14 12:30:00.000   2012-11-14 15:00:00.000
1   2012-11-15 07:00:00.000   2012-11-15 09:00:00.000
1   2012-11-15 09:00:00.000   2012-11-15 09:15:00.000
1   2012-11-15 09:15:00.000   2012-11-15 12:00:00.000
1   2012-11-15 12:30:00.000   2012-11-15 15:00:00.000

and want to get it preprcessed like:

timestamp               t   x

2012-11-13 07:00:00.000 1   1
2012-11-13 09:00:00.000 1   0
2012-11-13 09:00:00.000 1   1
2012-11-13 09:00:00.000 2   1
2012-11-13 09:15:00.000 1   0
2012-11-13 09:15:00.000 1   1
2012-11-13 12:00:00.000 1   0
2012-11-13 12:30:00.000 1   1
2012-11-13 15:00:00.000 1   0
2012-11-13 16:00:00.000 2   0
2012-11-14 07:00:00.000 1   1
2012-11-14 09:00:00.000 1   0
2012-11-14 09:00:00.000 1   1
2012-11-14 09:15:00.000 1   0
2012-11-14 09:15:00.000 1   1
2012-11-14 12:00:00.000 1   0
2012-11-14 12:30:00.000 1   1
2012-11-14 15:00:00.000 1   0
2012-11-15 07:00:00.000 1   1
2012-11-15 09:00:00.000 1   0
2012-11-15 09:00:00.000 1   1
2012-11-15 09:15:00.000 1   0
2012-11-15 09:15:00.000 1   1
2012-11-15 12:00:00.000 1   0
2012-11-15 12:30:00.000 1   1
2012-11-15 15:00:00.000 1   0

x should define, if value of timestamp is from starttime ( x = 1 ) or from endtime ( x = 0 ). i know, it's piece of cake, using two selects with an union all.

but i'm wondering if it is possible using unpivot() with some extensions? started like:

SELECT 
    , UD."timeStamp"
    , UD."t"
    , "isStart"
FROM 
    (
        SELECT 
              "StartTime"
            , "EndTime"
            , "t"
            , < REFERENCE TO STARTTIME OR ENDTIME ? >
        FROM 
            Data 
    ) AS BC
UNPIVOT ( "timeStamp" FOR dummy IN ( "StartTime", "EndTime" )) AS UD

< REFERENCE TO STARTTIME OR ENDTIME ? > should give the information, from which column timeStamp comes from...

Upvotes: 0

Views: 80

Answers (1)

Taryn
Taryn

Reputation: 247670

How about something like this, just apply a CASE statement to it:

select timestamp,
  t,
  case when col = 'starttime' then 1 else 0 end x
from yourtable
unpivot
(
  timestamp for col in (starttime, endtime)
) un

See SQL Fiddle with Demo

If you don't use the CASE statement the col field just displays either the value starttime or endtime.

Result:

|                       TIMESTAMP | T | X |
-------------------------------------------
| November, 13 2012 07:00:00+0000 | 1 | 1 |
| November, 13 2012 09:00:00+0000 | 1 | 0 |
| November, 13 2012 09:00:00+0000 | 1 | 1 |
| November, 13 2012 09:15:00+0000 | 1 | 0 |
| November, 13 2012 09:00:00+0000 | 2 | 1 |
| November, 13 2012 16:00:00+0000 | 2 | 0 |
| November, 13 2012 09:15:00+0000 | 1 | 1 |
| November, 13 2012 12:00:00+0000 | 1 | 0 |
| November, 13 2012 12:30:00+0000 | 1 | 1 |
| November, 13 2012 15:00:00+0000 | 1 | 0 |
| November, 14 2012 07:00:00+0000 | 1 | 1 |
| November, 14 2012 09:00:00+0000 | 1 | 0 |
| November, 14 2012 09:00:00+0000 | 1 | 1 |
| November, 14 2012 09:15:00+0000 | 1 | 0 |
| November, 14 2012 09:15:00+0000 | 1 | 1 |
| November, 14 2012 12:00:00+0000 | 1 | 0 |
| November, 14 2012 12:30:00+0000 | 1 | 1 |
| November, 14 2012 15:00:00+0000 | 1 | 0 |
| November, 15 2012 07:00:00+0000 | 1 | 1 |
| November, 15 2012 09:00:00+0000 | 1 | 0 |
| November, 15 2012 09:00:00+0000 | 1 | 1 |
| November, 15 2012 09:15:00+0000 | 1 | 0 |
| November, 15 2012 09:15:00+0000 | 1 | 1 |
| November, 15 2012 12:00:00+0000 | 1 | 0 |
| November, 15 2012 12:30:00+0000 | 1 | 1 |
| November, 15 2012 15:00:00+0000 | 1 | 0 |

Upvotes: 2

Related Questions