CODEWITHSUNDEEP
sql
FreshTransistor
FreshTransistor

Reputation: 109

get number of times values on the basis of input value

Input table

Col
1
2
2
3
3
4
5

Output

1
2
2
2
2
3
3
3
3
3
3
4
4
4
4
5
5
5
5
5

Is there any way this could be achieved in sql with and without writing any function ? Please take a note that, column can have duplicate values.

Upvotes: 0

Views: 38

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

Here is a standard SQL recursive query for this:

with cte (num, cnt) as
(
  select num, num from mytable
  union all
  select num, cnt - 1 from cte where cnt > 1
)
select num from cte order by num;

Upvotes: 1

Stu
Stu

Reputation: 32614

This is just a cross join to a numbers/tally table. You already have one in your source data, so you can simply use it as a distinct list:

select i.col
from input i
cross join (select distinct col as n from input) n
where n.n <= i.col
order by i.col

Upvotes: 1

user330315
user330315

Reputation:

you didn't state your DBMS product, but in Postgres this can be done using `generate_series()

select t.col
from the_table t
  cross join generate_series(1, t.col) 
order by t.col

Upvotes: 1

Related Questions