Hi,
I'm using Oracle 11 r2 and I have a table made like this :
create table TEST_CL_PLAN
(
id NUMBER,
week_day VARCHAR2(3),
from_hour VARCHAR2(5),
to_hour VARCHAR2(5),
contact VARCHAR2(400)
)
This table contains the data attached at the bottom.
In this data There are always 3 lines for each day of the week (therefore always 21 lines): one line for each hour interval (the intervals are always the same)
I would like to convert rows to columns in order to get a table like this:
Is it possible to obtain it with a query or is a PL / SQL procedure necessary?
I am trying to use the PIVOT function, but have not succeeded yet.
Is this the right way? Any suggestions will be greatly appreciated
thank you
regards
saverio
TEST DATA :
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Mon', '00:00', '08:30', 'alessio_claudio');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Tue', '00:00', '08:30', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Wed', '00:00', '08:30', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Thu', '00:00', '08:30', 'alessio_claudio');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Fri', '00:00', '08:30', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Sat', '00:00', '08:30', 'alessio_claudio');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Sun', '00:00', '08:30', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Mon', '08:30', '18:30', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Tue', '08:30', '18:30', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Wed', '08:30', '18:30', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Thu', '08:30', '18:30', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Fri', '08:30', '18:30', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Sat', '08:30', '18:30', 'alessio_claudio');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Sun', '08:30', '18:30', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Mon', '18:30', '24:00', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Tue', '18:30', '24:00', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Wed', '18:30', '24:00', 'alessio_claudio');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Thu', '18:30', '24:00', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Fri', '18:30', '24:00', 'alessio_claudio');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Sat', '18:30', '24:00', 'rep_dba_psm');
insert into TEST_CL_PLAN (id, week_day, from_hour, to_hour, contact)
values (48, 'Sun', '18:30', '24:00', 'alessio_claudio');