CODEWITHSUNDEEP
sqloracle-database
Oxana Grey
Oxana Grey

Reputation: 387

get numbers from left to right

Ive a diary where every page contains only single topic, not more than that

Input

PageNum | Title
1| A
2| B
4| C
5| D
7| E

want to read the book from left to right ( page 0 to page last), show null where there is no topic on page

left | right
null | A              /0,1
B    | null           /2,3
C    | D              /4,5
null | E              /6,7

So its a sequence from 0 to last page from left to right Kinda stuck to think how it can be solved ? any suggestions please

Upvotes: 0

Views: 83

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21075

Create first the page skelett of the book. Easy task for connect by levelonly the boundary must be set carefully.

select
 2*(rownum-1) left, 2*rownum-1 right
from dual connect by level <= (select ceil(max(pageNum+1)/2) from table_name); 

      LEFT      RIGHT
---------- ----------
         0          1
         2          3
         4          5
         6          7
         8          9
        10         11

Than use it simple with two outer joins:

with book as(         
select
 2*(rownum-1) left, 2*rownum-1 right
from dual connect by level <= (select ceil(max(pageNum+1)/2) from table_name)
)
select 
lt.Title title_left, rt.Title title_right
from book
left outer join table_name lt on book.left = lt.pageNum
left outer join table_name rt on book.right = rt.pageNum
order by left;

Upvotes: 1

MT0
MT0

Reputation: 167981

If you can skip entire double pages then you need a row generator to include the missing pages and can use:

SELECT MAX(CASE MOD(p.pagenum, 2) WHEN 0 THEN t.title END) AS left,
       MAX(CASE MOD(p.pagenum, 2) WHEN 1 THEN t.title END) AS right,
       LISTAGG(p.pagenum, ',') WITHIN GROUP (ORDER BY p.pagenum) AS pages
FROM   (
         SELECT LEVEL - 1 AS pagenum
         FROM   DUAL
         CONNECT BY LEVEL <= (SELECT CEIL(MAX(pagenum+1)/2)*2 FROM table_name)
       ) p
       LEFT OUTER JOIN table_name t
       ON (p.pagenum = t.PageNum)
GROUP BY
       FLOOR(p.pagenum/2)

Which, for the sample data:

CREATE TABLE table_name (PageNum, Title) AS
SELECT 1, 'A' FROM DUAL UNION ALL
SELECT 2, 'B' FROM DUAL UNION ALL
SELECT 4, 'C' FROM DUAL UNION ALL
SELECT 5, 'D' FROM DUAL UNION ALL
SELECT 7, 'E' FROM DUAL UNION ALL
SELECT 10, 'F' FROM DUAL;

Outputs:

LEFT RIGHT PAGES
null A 0,1
B null 2,3
C D 4,5
null E 6,7
null null 8,9
F null 10,11

db<>fiddle here

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Group by page number integer-divided by 2 to get the pairs.

select
  max(case when mod(pagenum, 2) = 0 then title end) as title_left,
  max(case when mod(pagenum, 2) = 1 then title end) as title_right
from mytable
group by trunc(pagenum / 2)
order by trunc(pagenum / 2);

The MAX is needed, because the DBMS does not know that there is only up to one row matching the modulo expression. You could just as well use MIN. It makes no difference. Use ANY_VALUE here, if you are alreay using Oracle 21c.

Here is the outer join solution that jarlh suggested in the comments:

with even as (select * from mytable where mod(pagenum, 2) = 0)
   , odd  as (select * from mytable where mod(pagenum, 2) = 1)
select
  even.title as title_left,
  odd.title as title_right
from even full outer join odd on odd.pagenum = even.pagenum + 1
order by coalesce(even.pagenum, odd.pagenum);

Demo: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=852cc5f8d45da8e832e44a9a48269c76

Upvotes: 2

Related Questions