Niks 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


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

Answer Source

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