Niks - 1 year ago 64
SQL Question

# How to generate decimal numbers sequence for particular number range

Consider following example for above question.
Suppose we have series of decimal numbers like

`(12.50 ,13.20 etc.)`

I want result as

``````12.51001
12.51002
....
13.19999
13.20000
``````

Upto 5 decimal places it to be generated.
Is it possible in sql ?

Here is one trick using `Recursive CTE`

`Cast` your data to `5` decimal places in `CTE` to get the result in required format

``````;WITH cte
AS (SELECT Cast(12.50 AS NUMERIC(22, 5)) AS num --Min value from your data
UNION ALL
SELECT Cast(num + 0.00001 AS NUMERIC(22, 5))
FROM   cte
WHERE  num < Cast(13.20 AS NUMERIC(22, 5))) -- Max value from your data
SELECT *
FROM   cte
OPTION (maxrecursion 0)
``````

In your expected result, Data starts from `12.51001` though your sample data starts from `12.50`. If you really need to start from `12.51001` then add `0.01001` to the source query of `CTE`

`````` Cast(12.50 +0.01001 AS NUMERIC(22, 5)) AS num
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download