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
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.
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.
