Multiple row to multiple column based on data in SQL Oracle
Multiple row to multiple column based on data in SQL Oracle
output will be as image
SQL Oracle query Sample
select * from (
select * from (
with fifteen as (select trunc(sysdate) + (level * 15)/(24*60) c_time from dual connect by level <= (24*60) / 15 )
select to_number(to_char(c_time, 'hh')) HR,to_char(c_time, 'hh24:mi')||' - '||to_char(c_time+ 15 / (24 * 60), 'hh24:mi') TimeSlots
from fifteen
where extract(hour from cast (c_time as timestamp)) between 2 and 5
) t
)
HR
YES 1ST COLUMN(4 POSSIBLE)
– Mukul
Sep 17 '18 at 11:39
Also, the output you want looks like something from Excel. It would be a bunch of work in Oracle to get numerical column headers with the rest of the columns bearing time ranges.
– Tim Biegeleisen
Sep 17 '18 at 11:40
@TimBiegeleisen If double quotes are used around a column alias then you can have anything as the alias including keywords or numbers (its just not good practice); so if numeric column aliases are required then just use
"2".– MT0
Sep 17 '18 at 12:20
"2"
@MT0 I was commenting under the (possibly incorrect) assumption that the OP actually wanted a literal header row in the result set. I said this because I was looking at an Excel snapshot, where the header row is actual data. But, you're right, if Oracle is like most other databases, using a column alias might do the trick.
– Tim Biegeleisen
Sep 17 '18 at 13:56
1 Answer
1
Oracle Query 1:
with fifteen as (
select CAST( TRUNC( sysdate ) AS TIMESTAMP ) + level * INTERVAL '15' MINUTE c_time,
MOD( LEVEL,4 ) AS quarter
from dual
connect by level <= (24*60) / 15
)
SELECT "2", "3", "4", "5"
FROM (
select EXTRACT( HOUR FROM c_time ) HR,
quarter,
to_char(c_time, 'hh24:mi')||' - '||to_char(c_time + INTERVAL '15' MINUTE, 'hh24:mi') TimeSlots
from fifteen
) t
PIVOT( MAX( timeslots ) FOR HR IN ( 2, 3, 4, 5 ) )
ORDER BY quarter
Oracle Query 2:
SELECT TO_CHAR(two, 'HH24:MI') || ' - ' || TO_CHAR(two +INTERVAL '15' MINUTE,'HH24:MI') AS "2",
TO_CHAR(three,'HH24:MI') || ' - ' || TO_CHAR(three+INTERVAL '15' MINUTE,'HH24:MI') AS "3",
TO_CHAR(four ,'HH24:MI') || ' - ' || TO_CHAR(four +INTERVAL '15' MINUTE,'HH24:MI') AS "4",
TO_CHAR(five ,'HH24:MI') || ' - ' || TO_CHAR(five +INTERVAL '15' MINUTE,'HH24:MI') AS "5"
FROM (
SELECT DATE '1970-01-01' + INTERVAL '2' HOUR + INTERVAL '15' MINUTE * (LEVEL - 1) AS two,
DATE '1970-01-01' + INTERVAL '3' HOUR + INTERVAL '15' MINUTE * (LEVEL - 1) AS three,
DATE '1970-01-01' + INTERVAL '4' HOUR + INTERVAL '15' MINUTE * (LEVEL - 1) AS four,
DATE '1970-01-01' + INTERVAL '5' HOUR + INTERVAL '15' MINUTE * (LEVEL - 1) AS five
FROM DUAL
CONNECT BY LEVEL <= 4
)
Output:
2 3 4 5
------------- ------------- ------------- -------------
02:00 - 02:15 03:00 - 03:15 04:00 - 04:15 05:00 - 05:15
02:15 - 02:30 03:15 - 03:30 04:15 - 04:30 05:15 - 05:30
02:30 - 02:45 03:30 - 03:45 04:30 - 04:45 05:30 - 05:45
02:45 - 03:00 03:45 - 04:00 04:45 - 05:00 05:45 - 06:00
sorry boss late response. Great man!!!
– Mukul
Sep 18 '18 at 11:26
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy
You are basically asking for some sort of pivot query. You should tell us how many possible different
HR(the first column) values there could be.– Tim Biegeleisen
Sep 17 '18 at 11:34