0

I have a table with one column duration containing integer values, and I'm trying to build an other column, using a sql query, that would contain an a list of integer between 1 and the value in the duration column.

For example:

duration | range
3        | [1, 2, 3]
3        | [1, 2, 3]
2        | [1, 2]
1        | [1]
...

I found a potential solution in JS.

create or replace function list_range(DURATION double)
  returns VARCHAR
  language javascript
  strict
  as 'return [...Array(DURATION).keys()];';

SELECT 
   t.*,
   list_range(t.duration) as range
FROM table t

What do you think of this solution? Can it be optimized?

Anatoli de BRADKE
  • 1
  • 1
  • Tag your question with the databas you are using. – Gordon Linoff Jul 22 '20 at 14:41
  • 2
    Does this answer your question? [How do I create a comma-separated list using a SQL query?](https://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query) – FlexYourData Jul 22 '20 at 14:42

1 Answers1

0
with temp as (
    select distinct duration, level as l
    from duration
    connect by level <= duration)
select duration,
       '['||listagg(l, ', ') within group(order by l)||']' as range
from temp
group by duration
order by 1 desc;
Rituraj28
  • 16
  • 1
  • While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Adrian Mole Jul 23 '20 at 15:48