convert the cols to rows in oracle by creating view in Oracle Apex

Question

I am working on create shift rota in Oracle Apex, I created a form to enter the details and that saved in a table, the table data looks like below.

I want to display the data as below in apex page.

how to transmit the data? I am trying to create a dynamic view , trying different way to make but no luck, please suggest.

so much easier if you post the actual create table statement with an insert statement instead of an image file. It takes you less than 2 minutes to generate that, it takes us longer to recreate your data. — Koen Lostrie, May 22 '20 at 17:12
Thank you for reply, Could you more specific what you are suggesting?. I am not using any image file to upload. I took a snapshot of the table data and posted for reference. — Srinivas Gadi, May 23 '20 at 02:18
In your question you have used images rather than runnable code. We need to type that by hand to reproduce your case... https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/ — Koen Lostrie, May 23 '20 at 05:34

score 0 · Answer 1 · answered May 22 '20 at 10:04

0

I believe you need unpivot. See this blog post:

https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot

And this one as well: http://www.grassroots-oracle.com/2014/08/demystifying-oracle-unpivot.html

answered May 22 '20 at 10:04

Chaitanya Koratamaddi

241
1
1

Koen Lostrie · Accepted Answer · 2020-05-23T12:37:54.110

0

You don't need a dynamic view... A month is always between 28 and 31 days so you could create a view that has 32 columns (employee + 31 days). Create a column per day with a case statement to determine that for that employee there is a shift. Here is an example for the 5 days of the month and the last 4 days of the month (you can easily fill in the remaining days):

WITH 
FUNCTION is_valid_date (date_str_i VARCHAR2, format_i VARCHAR2) RETURN VARCHAR2
/* check if date is valid */
AS
  l_dummy_dt DATE;
  date_not_valid_for_m EXCEPTION;
  PRAGMA EXCEPTION_INIT(date_not_valid_for_m, -01839);  
BEGIN
  SELECT TO_DATE(date_str_i,format_i) INTO l_dummy_dt FROM DUAL;
  RETURN 'Y';
EXCEPTION WHEN date_not_valid_for_m THEN
  RETURN 'N';
END; 
mymonth(monthname,yr) AS (SELECT 'FEB','2020' FROM dual)
,mydata (employee, shift, the_from, the_to) AS
(SELECT 'Deepa', 'A',TO_DATE('02-FEB-2020','DD-MON-YYYY'),TO_DATE('31-MAY-2020','DD-MON-YYYY') FROM DUAL
 UNION
 SELECT 'Srini', 'M',TO_DATE('02-FEB-2020','DD-MON-YYYY'),TO_DATE('04-MAY-2020','DD-MON-YYYY') FROM DUAL  
)
,myschedule (employee,day01,day02,day03,day04,day05,day28,day29,day30,day31)
AS
(
SELECT 
d.employee,
CASE WHEN TO_DATE('01-'||m.monthname||'-'||m.yr,'DD-MON-YYYY') BETWEEN d.the_from AND d.the_to THEN d.shift ELSE NULL END,
CASE WHEN TO_DATE('02-'||m.monthname||'-'||m.yr,'DD-MON-YYYY') BETWEEN d.the_from AND d.the_to THEN d.shift ELSE NULL END,
CASE WHEN TO_DATE('03-'||m.monthname||'-'||m.yr,'DD-MON-YYYY') BETWEEN d.the_from AND d.the_to THEN d.shift ELSE NULL END,
CASE WHEN TO_DATE('04-'||m.monthname||'-'||m.yr,'DD-MON-YYYY') BETWEEN d.the_from AND d.the_to THEN d.shift ELSE NULL END,
CASE WHEN TO_DATE('05-'||m.monthname||'-'||m.yr,'DD-MON-YYYY') BETWEEN d.the_from AND d.the_to THEN d.shift ELSE NULL END,
CASE WHEN TO_DATE('28-'||m.monthname||'-'||m.yr,'DD-MON-YYYY') BETWEEN d.the_from AND d.the_to THEN d.shift ELSE NULL END,
CASE WHEN is_valid_date(date_str_i => '29-'||m.monthname||'-'||m.yr,format_i => 'DD-MON-YYYY') = 'Y' 
  THEN
    CASE WHEN TO_DATE('29-'||m.monthname||'-'||m.yr,'DD-MON-YYYY') BETWEEN d.the_from AND d.the_to THEN d.shift ELSE NULL END
  ELSE 
    NULL
  END,
CASE WHEN is_valid_date(date_str_i => '30-'||m.monthname||'-'||m.yr,format_i => 'DD-MON-YYYY') = 'Y' 
  THEN
    CASE WHEN TO_DATE('30-'||m.monthname||'-'||m.yr,'DD-MON-YYYY') BETWEEN d.the_from AND d.the_to THEN d.shift ELSE NULL END
  ELSE 
    NULL
  END,
CASE WHEN is_valid_date(date_str_i => '31-'||m.monthname||'-'||m.yr,format_i => 'DD-MON-YYYY') = 'Y' 
  THEN
    CASE WHEN TO_DATE('31-'||m.monthname||'-'||m.yr,'DD-MON-YYYY') BETWEEN d.the_from AND d.the_to THEN d.shift ELSE NULL END
  ELSE 
    NULL
  END

FROM mydata d,mymonth m
)
SELECT * FROM myschedule;

Now you're left with 1 issue and that is how to merge the rows for Krish and Rahul. You could do something like shown here: Oracle SQL: Merge rows into single row

edited May 23 '20 at 12:37

answered May 22 '20 at 18:01

Koen Lostrie

14,938
2
13
19

Thank you so much to nail down the issue, that exactly what I am looking for. coming to the 1st issue, I can print the days of a month by parsing the month and year to the below code, so that it tells whether a month have 29/30/31. but confusing where exactly I can fit this code in the given code. `SELECT TO_DATE ('01-' || '&Month' || '-' || '&year', 'DD-MON-YYYY') + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 31 AND TO_CHAR ( TO_DATE ('01-' || '&Month' || '-' || '&year', 'DD-MON-YYYY') + LEVEL - 1, 'MON') = '&Month'` – Srinivas Gadi May 23 '20 at 11:09
If it is what you are looking for can you please mark the answer as correct ? – Koen Lostrie May 23 '20 at 11:10
That codes generates one day per row. What do you want to use it for ? You no longer need it because in my solution, you have 1 column per day of the month. ```CASE WHEN TO_DATE('01-'||m.monthname||'-'||m.yr,'DD-MON-YYYY') BETWEEN d.the_from AND d.the_to THEN d.shift ELSE NULL END,``` is for day 1, the next column for day 2 etc. You no longer need other code to generate each day. – Koen Lostrie May 23 '20 at 11:17
updated my answer to include columns for day 28 thru 31 with a check for valid dates – Koen Lostrie May 23 '20 at 12:38
Thank you very much, last few more concern.**1)** Since we are updating the NAME ( this case 'Deepa') and start and end dates manually here `(SELECT 'Deepa', 'A',TO_DATE('02-FEB-2020','DD-MON-YYYY'),TO_DATE('31-MAY-2020','DD-MON-YYYY') FROM DUAL` , is it possible to refer directly from table `myschedule` **2)** rather mention the `dayNN` (`(employee,day01,day02,day03,day04,day05,day28,day29,day30,day31)`),is it possible to replace the 'dayNN` with month name as like `May01`..,take month name and date from start/end date from table.final goal to make it dynamic rather manual for other months – Srinivas Gadi May 24 '20 at 08:51
I am sorry, you really helped a lot. marked it as answered. if you could have some time, kindly respond to my latest concern. – Srinivas Gadi May 24 '20 at 14:34
answers: 1) due to lack of reproducible case I had put test data in the subquery called "mydata". You're supposed to replace that with your data or select from that table. This is just an example, it is up to you to implement it and rewrite however you see fit. 2) Sure you can. Use/change it any way you like. But if you change the aliases to May01, May02... what are you going to do in June ? Or you could add 2 additional columns. One for month and one for year, it is all up to you – Koen Lostrie May 24 '20 at 16:29
In second case, I don't want to hard code with Month name, that month name should take it from given date, ex: `THE_FROM : 05/01/202` -> extract that 05 from date and convert that to equivalent month (this case 05 means May), so that it will dynamic and don't want to edit manually in future. – Srinivas Gadi May 25 '20 at 02:59
Here is the [Data](https://docs.google.com/document/d/1NQ7H9BpYk5iUBE5j6wb_8DSASmDnej8JkPJFIkTtdGs/) , to create and generate the data for table to reproduce. Thank you. – Srinivas Gadi May 25 '20 at 06:12
You cannot make column names of a query dynamic. – Koen Lostrie May 25 '20 at 06:24

convert the cols to rows in oracle by creating view in Oracle Apex

2 Answers2