CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Avinash
Avinash

Reputation: 11

Want to display 1 column values multiple times

I am using ORACLE 11g. I have table like this

col1     col2
john      1
david     2
root      3
baily     4

I want output like below table to print 1 row multiple times

col1     col2
john      1
david     2
david     2
root      3
root      3
root      3
baily     4
baily     4
baily     4
baily     4

Upvotes: 0

Views: 729

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

You can use a hierarchical query presuming the name values(col1) are unique throughout the table such as

 SELECT * 
   FROM t
CONNECT BY level <= col2   
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR col1 = col1
  ORDER BY col2

Demo

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142743

With sample data you posted

SQL> select * From test order by col2;

COL1        COL2
----- ----------
john           1
david          2
root           3
baily          4

query that returns desired result might be

SQL> select col1, col2
  2  from test cross join table(cast(multiset(select level from dual
  3                                           connect by level <= col2
  4                                          ) as sys.odcinumberlist))
  5  order by col2;

COL1        COL2
----- ----------
john           1
david          2
david          2
root           3
root           3
root           3
baily          4
baily          4
baily          4
baily          4

10 rows selected.

SQL>

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35583

I have assumed you are using Oracle 11g (i.e. the SQL syntax below is for Oracle)

Joining your data to a "numbers table" will repeat each row according to the value in col2. For example:

CREATE TABLE mytable(
   col1 VARCHAR(5) NOT NULL PRIMARY KEY
  ,col2 INTEGER  NOT NULL
);
INSERT INTO mytable(col1,col2) VALUES ('john',1);
INSERT INTO mytable(col1,col2) VALUES ('david',2);
INSERT INTO mytable(col1,col2) VALUES ('root',3);
INSERT INTO mytable(col1,col2) VALUES ('baily',4);


select col1, col2
from mytable t
inner join (
   select 1 as n from dual union all
   select 2 as n from dual union all
   select 3 as n from dual union all
   select 4 as n from dual
   ) n on t.col2 >= n.n
   order by col2, col1


+-------+------+
| COL1  | COL2 |
+-------+------+
| john  |    1 |
| david |    2 |
| david |    2 |
| root  |    3 |
| root  |    3 |
| root  |    3 |
| baily |    4 |
| baily |    4 |
| baily |    4 |
| baily |    4 |
+-------+------+

nb: It is possible to form the needed "numbers table" several different ways.

e.g. use any existing table with sufficient rows in it for your purpose

select row_number() over(order by table_name) as n
from all_tables

demo

Upvotes: 2

Related Questions