I have a table having single column named combined_data as shown below :-
101
SAM
INDIA
102
ROCK
USA
103
MICHAEL
RUSSIA
AND I NEED THE OUTPUT LIKE:-
101 SAM INDIA
102 ROCK USA
103 MICHAEL RUSSIA
How could I achieve this? Tried using pivot but it fetches min and max records so I get only 2 rows I need all the rows...
Just number your rows, precompute index of each new row and column and put it together.
with combined_data (id,val) as (
select 1, '101' from dual union all
select 2, 'SAM' from dual union all
select 3, 'INDIA' from dual union all
select 4, '102' from dual union all
select 5, 'ROCK' from dual union all
select 6, 'USA' from dual union all
select 7, '103' from dual union all
select 8, 'MICHAEL' from dual union all
select 9, 'RUSSIA' from dual
), n as (
select id, val, row_number() over (order by id) - 1 as rn
from combined_data
), rc as (
select id, val, mod(rn, 3) as c, (rn - mod(rn, 3))/ 3 as r
from n
)
select t1.val, t2.val, t3.val
from rc t1 join rc t2 using (r) join rc t3 using (r)
where t1.c = 0 and t2.c = 1 and t3.c = 2
UPDATE: the solution based on pivot clause:
with combined_data (id,val) as (
select 1, '101' from dual union all
select 2, 'SAM' from dual union all
select 3, 'INDIA' from dual union all
select 4, '102' from dual union all
select 5, 'ROCK' from dual union all
select 6, 'USA' from dual union all
select 7, '103' from dual union all
select 8, 'MICHAEL' from dual union all
select 9, 'RUSSIA' from dual
), n as (
select id, val, row_number() over (order by id) - 1 as rn
from combined_data
), rc as (
select val, mod(rn, 3) as c, (rn - mod(rn, 3))/ 3 as r
from n
)
select num, name, state from rc
pivot (min(val) for c in (0 num, 1 name, 2 state))
order by r
(Note the rc CTE must not have id column here. Otherwise it would be part of implicit group by performed by pivot clause.)
See db fiddle.
id there.
