Search code examples
sql-servergenerate-series

Generate Series number depend on Input


Please let me know how to generate following series in SQL ,I have input and need output such as : This is my Input and needed output


Solution

  • You could use FLOOR like this

    ;WITH temp AS
    (
       SELECT row_number() over(ORDER BY n.t) AS Input
       FROM (VALUES (1),(2), (3),(4),(5),(6),(7),(8),(9),(10)) AS n(t)
       CROSS JOIN (VALUES (1),(2), (3),(4),(5),(6),(7),(8),(9),(10)) AS n1(t)
    ) -- return input table  from 0 --> 100
    SELECT t.Input, (FLOOR((t.Input - 1)/15) + 1) *15 AS Output
    FROM temp t
    

    Demo link: http://rextester.com/NYZ63298