CODEWITHSUNDEEP
sqloracle-database
Ganesh
Ganesh

Reputation: 506

SQL query to select column1 value multiple times based on column 2 value

How can I write a query for below scenario in Oracle?

Input

Column1 Column2
R        1
S        2
F        3

Output

RSSFFF

i.e. 'R' 1 time, 'S' 2 time and 'F' 3 times.

Upvotes: 1

Views: 253

Answers (2)

Junjie
Junjie

Reputation: 521

You can use rpad to repeat the letters, and listagg to concatenate them in a single line, like so.

select
  listagg(rpad(column1,column2,column1)) within group (order by column2)
from table_name;

But this would work if you have another column to order the rows, in this case, I just use the column2.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269443

One method uses lpad():

select lpad(column1, column2, column1)
from t;

If you want a single string, then use listagg():

select listagg(lpad(column1, column2, column1)) within group (order by null)
from t;

Upvotes: 2

Related Questions