SQL server generate number from 1 to nth

Question

I have a table like;

**ID** **CASH** **INTERVAL**
  1       60     5
  2       10     3
  3       20     4

I want to add 2 columns deriving from current ones like; Column MULT means I list numbers from 1 to INTERVAL by commas and for VAL value I substract CASH from 100 and divide it by INTERVAL and list those intervals by comma listed values inside column VAL

**ID** **CASH** **INTERVAL**     **MULT**          **VAL**
  1       60         5           1,2,3,4,5          8,8,8,8,8
  2       10         3           1,2,3              30,30,30
  3       20         4           1,2,3,4            20,20,20,20

I know it looks like not an informative question but at least anyone know about to list them in single column with commas using STUFF or etc?

Having comma separated values in a single field is a code-smell. *(Non atomic data in an atomic entity.)* This means that you'll find support for this is quite poor *(Even MS tend to try not to give good support to bad idea)*. May I ask ***why*** it is that you want to do this? What functionality are you trying to achieve? — MatBailie, Aug 18 '15 at 13:44
Why do you want to do this? Putting multiple values in a single column is nearly always not the best approach. — Eric Hauenstein, Aug 18 '15 at 13:45
possible duplicate of [Generating a sequence in sql server](http://stackoverflow.com/questions/5279071/generating-a-sequence-in-sql-server) — Julien Vavasseur, Aug 18 '15 at 13:47
How large do these numbers get? And why does the interval change on the last one? — Gordon Linoff, Aug 18 '15 at 13:56
@GordonLinoff sorry I have edited it now again, it was modification after a while thats why it remained there — canpoint, Aug 18 '15 at 13:59

Gordon Linoff · Accepted Answer · 2015-08-18T22:39:46.663

2

Given how you phrase the question and the sample data you provide, I would be tempted to use a very bespoke approach for this:

with params as (
      select '1,2,3,4,5,6,7,8,9' as numbers,
             'x,x,x,x,x,x,x,x,x' as vals
     )
select l.*,
       left(numbers, interval * 2 - 1) as mult,
       replace(left(vals, interval * 2 - 1), 'x', (100 - cash) / interval) as val
from params cross join
     [like] l;

Of course, you might need to extend the strings in the CTE, if they are not long enough (and this might affect the arithmetic).

The advantage to this approach is speed. It should be pretty fast.

Note: you can also use replicate() rather than the vals.

edited Aug 18 '15 at 22:39

answered Aug 18 '15 at 13:59

Gordon Linoff

1,242,037
58
646
786

May want to change the `\``'s to `[]` since it's SQL Server. – Siyual Aug 18 '15 at 14:06
thanks for the reply, the numbers you write in cte is not derived from 9, written directly as value. How can I achieve it? – canpoint Aug 18 '15 at 14:09
Its the drawback of SO, u have only 1 upvote, Otherwise @gordonLinoff, u have recieved 1000000000000 of upvotes till now from my side. – Ankit Bajpai Aug 18 '15 at 14:45

SQL server generate number from 1 to nth

1 Answers1