CODEWITHSUNDEEP
sqloracle-database
Jim
Jim

Reputation: 609

Create a string of sequential numbers in an oracle SQL query

Using oracle SQL I want to generate a string list of numbers counting up to a quantity value.

Select [Item], [Quantity], <Something here> as [Quantity List]
from table

to return

[Item],[Quantity], [Quantity List]
'Socks', 2 , '1 2'
'Shoes', 3 , '1 2 3'
'Coats', 6 , '1 2 3 4 5 6'
'Hats' , 3 , '1 2 3'   etc...

I'm not trying to create a reference table, each quantity needs to be evaluated and appropriate string put in as it runs Thanks in advance

Upvotes: 2

Views: 476

Answers (2)

Popeye
Popeye

Reputation: 35900

You can use hiearchical query in sub query of select clause as follows:

select item, quantity,
       (select listagg(level, ' ') within group(order by level)
         from dual connect by level <= quantity) as quantity_list
  from t

Db<>Fiddle for the same.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I would suggest generating all the numbers first and then joining in. You can generate the list using a recursive CTE:

with cte(n, max_qty, list) as (
      select 1, max_qty, '1'  as list
      from (select max(quantity) as max_qty from t) x
      union all
      select n + 1, max_qty, list || ' ' || (n + 1) 
      from cte
      where n < max_qty
     )
select t.*, cte.list
from t join
     cte
     on t.quantity = cte.n;

Here is a db<>fiddle.

Note: If you really are using SQL Server, you can use very similar logic in that database.

Upvotes: 2

Related Questions