How to create a calendar from rows format?

Question

I've a table like this:

ID  DATE     ROOM    OWNER  FROM_TIME  TO_TIME

1   jan-1-18 Room_A  Jack   09:00      10:00
2   jan-1-18 Room_A  John   10:00      11:00
3   jan-1-18 Room_B  John   09:00      10:00

etc.

How can I pivot data to have :

Hour        Room_A       Room_B
09:00       Jack         John     
10:00       John
11:00       ....

I know I've to use some "pivot" function, but i don't know which!

Thanks

This may help: https://stackoverflow.com/questions/10025934/how-to-pivot-text-columns-in-sql-server. Also some examples here: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Joe, Jan 11 '18 at 15:56

score 1 · Accepted Answer · answered Jan 11 '18 at 16:17

Here is one way, using the pivot operator (not function!) - so this solution requires Oracle 11.1 or higher. For earlier versions, you can use conditional aggregation. (Actually I allowed for the possibility of more than one "owner" for the same room and same time slot, with the LISTAGG() function, so this will require Oracle version 11.2. You can replace that with MAX() if there is at most one owner for each time slot, and that will work in 11.1.)

with
  test_data ( id, dt, room, owner, from_time, to_time ) as (
    select 1, 'jan-1-18', 'Room_A', 'Jack', '09:00', '10:00' from dual union all
    select 2, 'jan-1-18', 'Room_A', 'John', '10:00', '11:00' from dual union all
    select 3, 'jan-1-18', 'Room_B', 'John', '09:00', '10:00' from dual
  )
select dt, from_time, to_time, room_a, room_b
from   ( select dt, room, owner, from_time, to_time from test_data )
pivot  ( listagg(owner, ',') within group (order by owner)   -- or just max(owner)
                             for room in ('Room_A' as room_a, 'Room_B' as room_b) )
order by dt, from_time
;

DT       FROM_TIME TO_TIME ROOM_A ROOM_B
-------- --------- ------- ------ ------
jan-1-18 09:00     10:00   Jack   John
jan-1-18 10:00     11:00   John

Alessandro Cucina · Answer 2 · 2018-01-12T11:09:55.353

assuming that between from_time and to_time can pass more than one hour and that more names are booked for the same room in the same hour, try to use the pivot operator with the addition of the with construct in the following way.

You have to know all the possible values for room.

ID  DATE     ROOM    OWNER  FROM_TIME  TO_TIME

1   jan-1-18 Room_A  Jack   09:00      10:00
2   jan-1-18 Room_A  John   10:00      11:00
3   jan-1-18 Room_B  John   09:00      10:00
4   jan-1-18 Room_A  Jill   09:00      11:00

I added the "Jill" case that occupies room A from 09:00 to 11:00: the query shows the two hours separately:

with data as
     (select level-1 l from dual connect by level <= 100),
    dtset as
     (select id_row, day, room, owner,
             to_char(to_date(from_time, 'hh24:mi') + (l/24), 'hh24:mi') from_time,
             to_char(to_date(from_time, 'hh24:mi') + ((l+1)/24), 'hh24:mi') to_time
        from your_table_name, data
       where l < (24 * (to_date(to_time, 'hh24:mi') - to_date(from_time, 'hh24:mi'))))
select *
  from (select day, from_time, to_time, room, owner
          from dtset)   
        pivot (listagg(owner, ', ') within group (order by owner) for room in ('Room_A' as Room_A, 'Room_B' as Room_B))
  order by day, to_time;

in short, materialized dtset divides the two-hour time slot into TWO one-hour slots. Result:

DATA        FROM_TIME   TO_TIME   ROOM_A         ROOM_B
----------  ---------   --------  ----------     -------
11/01/2018  09:00       10:00     Jack, Jill     John
11/01/2018  10:00       11:00     Jill, John

How to create a calendar from rows format?

2 Answers2