Thanks for the question, Ravi.

Asked: October 04, 2018 - 2:04 am UTC

Last updated: November 05, 2018 - 1:36 am UTC

Version: 12c

Viewed 1000+ times

You Asked

i have a table with column name as value. There are 10 rows in the table. The desired output of this to be displayed as two columns first 5 rows as one column A and rows 6 to 10 as column B , next to each other as 5 rows of data like this

A  B
------------------------
ABN001001   ABN001006
ABN001002   ABN001007
ABN001003   ABN001008
ABN001004   ABN001009
ABN001005   ABN001010

script for the data

create table Test1
(id number, value varchar(40));

begin
insert into Test1 (id,value) values (1000,'ABN001001');
insert into Test1 (id,value) values (1002,'ABN001002');
insert into Test1 (id,value) values (1003,'ABN001003');
insert into Test1 (id,value) values (1004,'ABN001004');
insert into Test1 (id,value) values (1005,'ABN001005');
insert into Test1 (id,value) values (1006,'ABN001006');
insert into Test1 (id,value) values (1007,'ABN001007');
insert into Test1 (id,value) values (1008,'ABN001008');
insert into Test1 (id,value) values (1009,'ABN001009');
insert into Test1 (id,value) values (1010,'ABN001010');
end;

Thanks
Ravi

and Chris said...

Here's one way to do it:

- Assign a row number to each row
- Split the rows into the groups 1-5, 6-10 by dividing this number by five. And taking the ceiling of it
- Also get the value of this row number mod 5

You can then pivot by the values from the first calculation. This splits them into two columns. You need the mod two split the groups into five rows. This is because the database adds an implicit group by for every column not in the pivot clause

Which gives:

with rws as (
  select ceil ( row_number () over ( order by id ) / 5 ) grp,
         mod ( row_number () over ( order by id ), 5  ) rw,
         value
  from   test1 t
)
  select * 
  from   rws 
  pivot (
    max ( value ) for grp in ( 1 a, 2 b)
  )
  order by a;

RW   A           B           
   1 ABN001001   ABN001006   
   2 ABN001002   ABN001007   
   3 ABN001003   ABN001008   
   4 ABN001004   ABN001009   
   0 ABN001005   ABN001010

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Ravi, October 04, 2018 - 2:45 pm UTC

Thanks Tom. A perfect solution that will fit my need .

October 05, 2018 - 3:42 am UTC

glad we could help

same sql but needs to be grouped

Ravi, October 07, 2018 - 11:48 am UTC

Tom,
Thanks for your help. i got a head start. Extending on the earlier result, i need further to be grouped by another column called class _id.
================
create table Test1
(id number, value varchar(40),class_id number);

begin
insert into Test1 (id,value,Class_id) values (1000,'ABN001001','555');
insert into Test1 (id,value,Class_id) values (1002,'ABN001002','555');
insert into Test1 (id,value,Class_id) values (1003,'ABN001003','555');
insert into Test1 (id,value,Class_id) values (1004,'ABN001004','555');
insert into Test1 (id,value,Class_id) values (1005,'ABN001005','555');
insert into Test1 (id,value,Class_id) values (1006,'ABN001006','555');
insert into Test1 (id,value,Class_id) values (1007,'ABN001007','555');
insert into Test1 (id,value,Class_id) values (1008,'ABN001008','555');
insert into Test1 (id,value,Class_id) values (1009,'ABN001009','555');
insert into Test1 (id,value,Class_id) values (1010,'ABN001010','555');
insert into Test1 (id,value,Class_id) values (1011,'XYZ001011','777');
insert into Test1 (id,value,Class_id) values (1012,'XYZ001012','777');
insert into Test1 (id,value,Class_id) values (1013,'XYZ001013','777');
insert into Test1 (id,value,Class_id) values (1014,'XYZ001014','777');
insert into Test1 (id,value,Class_id) values (1015,'XYZ001015','777');
insert into Test1 (id,value,Class_id) values (1016,'XYZ001016','777');
insert into Test1 (id,value,Class_id) values (1017,'XYZ001017','777');
insert into Test1 (id,value,Class_id) values (1018,'XYZ001018','777');
insert into Test1 (id,value,Class_id) values (1019,'XYZ001019','777');
insert into Test1 (id,value,Class_id) values (1020,'XYZ001020','777');
end;

i have a table with column name as value. There are 10 rows in the table. The desired output of this to be displayed as two columns first 5 rows as one column and rows 6 to 10 as column b , next to each other as 5 rows of data
for each of the class_id
Class_id A B
------------------------------
555 ABN001001 ABN001006
555 ABN001002 ABN001007
555 ABN001003 ABN001008
555 ABN001004 ABN001009
555 ABN001005 ABN001010
777 XYZ001001 XYZ001006
777 XYZ001002 XYZ001007
777 XYZ001003 XYZ001008
777 XYZ001004 XYZ001009
777 XYZ001005 XYZ001010

========================================

October 08, 2018 - 10:19 am UTC

A couple of things:

- If you select class_id in your with clause, it'll form an implicit group by for your pivot, so appear
- You need to start the counter from one for each class_id. Do this by adding it in the partition by clause for row_number():

row_number () over ( partition by class_id order by id )

This splits the rows up into separate groups for each class_id.

Perfect

Ravi, October 08, 2018 - 2:09 pm UTC

Thanks for your help again.
I changed tot he following and works as expected
-------
with rws as (
select ceil ( row_number () over ( partition by class_id order by class_id,id ) / 5 ) grp,
--mod ( row_number () over (partition by class_id order by id), 5 ) rw,
mod ( row_number () over ( order by id ), 5 ) rw,
value,class_id
from test1 t
)
select *
from rws
pivot (
max ( value ) for grp in ( 1 a, 2 b,3 c,4 d)
)
order by a;
-------
1 555 ABN001001 ABN001006
2 555 ABN001002 ABN001007
3 555 ABN001003 ABN001008
4 555 ABN001004 ABN001009
0 555 ABN001005 ABN001010
1 777 XYZ001011 XYZ001016
2 777 XYZ001012 XYZ001017
3 777 XYZ001013 XYZ001018
4 777 XYZ001014 XYZ001019
0 777 XYZ001015 XYZ001020

October 08, 2018 - 4:41 pm UTC

Great, glad you got this working.

Need the sql in a different way

Ravi, November 01, 2018 - 8:09 pm UTC

Tom,
Thanks for your previous answers. Need your help with another sql in a different way

I want the data to be selected as 5 columns a,b,c,d,e, with the data from the rows (column, value of table test2) as a,b,c,d,e sequentially till all the data is read from the table test2.

Need sql output like this
-------------------------
a b c d e
----------------------------------------------------------
ABN001001 ABN001002 ABN001003 ABN001004 ABN001005
ABN001006 ABN001007 ABN001008 ABN001009 ABN001010
XYZ001011 XYZ001012 XYZ001013 XYZ001014 XYZ001015
XYZ001016 XYZ001017 XYZ001018

---------------------------------
create table test2
(id number, value varchar(40),class_id number);

begin
insert into test2 (id,value,Class_id) values (1000,'ABN001001','555');
insert into test2 (id,value,Class_id) values (1002,'ABN001002','555');
insert into test2 (id,value,Class_id) values (1003,'ABN001003','555');
insert into test2 (id,value,Class_id) values (1004,'ABN001004','555');
insert into test2 (id,value,Class_id) values (1005,'ABN001005','555');
insert into test2 (id,value,Class_id) values (1006,'ABN001006','555');
insert into test2 (id,value,Class_id) values (1007,'ABN001007','555');
insert into test2 (id,value,Class_id) values (1008,'ABN001008','555');
insert into test2 (id,value,Class_id) values (1009,'ABN001009','555');
insert into test2 (id,value,Class_id) values (1010,'ABN001010','555');
insert into test2 (id,value,Class_id) values (1011,'XYZ001011','777');
insert into test2 (id,value,Class_id) values (1012,'XYZ001012','777');
insert into test2 (id,value,Class_id) values (1013,'XYZ001013','777');
insert into test2 (id,value,Class_id) values (1014,'XYZ001014','777');
insert into test2 (id,value,Class_id) values (1015,'XYZ001015','777');
insert into test2 (id,value,Class_id) values (1016,'XYZ001016','777');
insert into test2 (id,value,Class_id) values (1017,'XYZ001017','777');
insert into test2 (id,value,Class_id) values (1018,'XYZ001018','777');
end;

columns to rows

Ravi, November 02, 2018 - 12:32 am UTC

slight change
-----------------
I want the data to be selected as 5 columns a,b,c,d,e. with the data from the rows (column value of table test2) as a,b,c,d,e sequentially till all the data is read from teh table test2 and grouped by class_id

class a b c d e
-------------------------------------------------------------
555 ABN001001 ABN001002 ABN001003 ABN001004 ABN001005
555 ABN001006 ABN001007 ABN001008 ABN001009 ABN001010
777 XYZ001011 XYZ001012 XYZ001013 XYZ001014 XYZ001015
777 XYZ001016 XYZ001017 XYZ001018

November 02, 2018 - 5:01 pm UTC

Then you need to pivot by the mod instead of the ceil/5:

with rws as (
select ceil ( row_number () over ( partition by class_id order by class_id,id ) / 5 ) grp,
mod ( row_number () over ( partition by class_id order by id ) - 1, 5 ) rw,
value,class_id
from test2 t
)
select * 
from rws 
pivot (
max ( value ) for rw in ( 0 a, 1 b,2 c,3 d, 4 e)
)
order by a;

GRP   CLASS_ID   A           B           C           D           E           
    1        555 ABN001001   ABN001002   ABN001003   ABN001004   ABN001005   
    2        555 ABN001006   ABN001007   ABN001008   ABN001009   ABN001010   
    1        777 XYZ001011   XYZ001012   XYZ001013   XYZ001014   XYZ001015   
    2        777 XYZ001016   XYZ001017   XYZ001018   <null>      <null>

Thanks

Ravi, November 02, 2018 - 6:55 pm UTC

Tom,
Thanks for the answer. Now i ma getting a feel of how pivot works.

Ravi

November 05, 2018 - 1:36 am UTC

glad we could help

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks

same sql but needs to be grouped

Perfect

Need the sql in a different way

columns to rows

Thanks

More to Explore

Analytics

SQL Query to Convert Ten Rows with One Column to Five Rows With One Column

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks

same sql but needs to be grouped

Perfect

Need the sql in a different way

columns to rows

Thanks

More to Explore

Analytics