SQL puzzle
Names table
nameData creation script
create table Names (
name char (10),
primary key (name));
insert into Names values ('Al');
insert into Names values ('Ben');
insert into Names values ('Charlie');
insert into Names values ('David');
insert into Names values ('Ed');
insert into Names values ('Frank');
insert into Names values ('Greg');
insert into Names values ('Howard');
insert into Names values ('Ida');
insert into Names values ('Joe');
insert into Names values ('Ken');
insert into Names values ('Larry');
insert into Names values ('Mike');
commit;
-------
Al
Ben
Charlie
David
Ed
Frank
Greg
Howard
Ida
Joe
Ken
Larry
Mike
Name column of Names table
It displays every 5 lines on one line (the remaining column of the last line is null)
Both rows and columns are output in ascending order of the name column in the Names table.
Output example (Convert two rows to one row)
name1 name2
------- ------
Al Ben
Charlie David
Ed Frank
Greg Howard
Ida Joe
Ken Larry
Mike null
Output example (Converting three lines to one line)
name1 name2 name3
----- ------ -------
Al Ben Charlie
David Ed Frank
Greg Howard Ida
Joe Ken Larry
Mike null null
Output example (Convert 4 rows to 1 row)
name1 name2 name3 name4
----- ---- - ------- ------
Al Ben Charlie David
Ed Frank Greg Howard
Ida Joe Ken Larry
Mike null null null
Output example (Convert five rows to one row)
name1 name2 name3 name4 name5
----- ---- - ------- ----- -----
Al Ben Charlie David Ed
Frank Greg Howard Ida Joe
Ken Larry Mike null null
Note:- Can I get an SQL code from Oracle 18c , from latest version..Am very exiting to get this solution from Oracle 18c...I knew the answer for this in an old style like..10g....if solution will be there from 18c side, please get it in here..Thanks a lot...
No need for 18c. You can do this in 11g!
To convert the rows into N columns:
- Assign every Nth row a number from 1..N
- Group the rows in batches of N and give each group a new value
You can do this by taking the mod and ceil of it's row_number, e.g.:
mod(row_number() over (order by name)-1, :cols)+1 rn,
ceil(row_number() over (order by name)/:cols) cl
With the rows normalized like this, you can paste the values 1..N in the pivot IN clause. The ceil grouping is needed to split out rows with the same column value. Otherwise the implicit group by lumps them all together.
Which gives you:
var cols number;
exec :cols := 3;
with rws as (
select n.*,
mod(row_number() over (order by name)-1, :cols)+1 rn,
ceil(row_number() over (order by name)/:cols) cl
from names n
)
select * from rws
pivot (
min(name) for rn in (1,2,3,4,5)
)
order by cl;
CL 1 2 3 4 5
1 Al Ben Charlie <null> <null>
2 David Ed Frank <null> <null>
3 Greg Howard Ida <null> <null>
4 Joe Ken Larry <null> <null>
5 Mike <null> <null> <null> <null>
exec :cols := 4;
with rws as (
select n.*,
mod(row_number() over (order by name)-1, :cols)+1 rn,
ceil(row_number() over (order by name)/:cols) cl
from names n
)
select * from rws
pivot (
min(name) for rn in (1,2,3,4,5)
)
order by cl;
CL 1 2 3 4 5
1 Al Ben Charlie David <null>
2 Ed Frank Greg Howard <null>
3 Ida Joe Ken Larry <null>
4 Mike <null> <null> <null> <null>
exec :cols := 5;
with rws as (
select n.*,
mod(row_number() over (order by name)-1, :cols)+1 rn,
ceil(row_number() over (order by name)/:cols) cl
from names n
)
select * from rws
pivot (
min(name) for rn in (1,2,3,4,5)
)
order by cl;
CL 1 2 3 4 5
1 Al Ben Charlie David Ed
2 Frank Greg Howard Ida Joe
3 Ken Larry Mike <null> <null> OK, so you have trailing null columns. And the upper limit for the number columns is fixed by however many values you specify in the IN clause. But you can always exclude unneeded columns from your select.