TSQL to create multiple lines in result set

• Stephen crocker

  SSCrazy

  Points: 2512

  More actions

  May 5, 2016 at 8:23 am

  #326795

  I have a need to create 49 points for a Viz. What i'm needed is this

  t = a value between -6 and 6 in .25 increments (which is 49 values)

  so it would look something like this

  Year Region t Rank

  2013 South 6.0 1

  2013 South 6.25 1

  etc.....

  2014 South 6.0 4

  2014 South 6.25 4

  Does anyone know how to add a dynamic column that would use the formula mentioned above basically start a -6 and add .25 then take that value of the previous and add .25 and so on until 49 points have been created

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 5, 2016 at 8:33 am

  #1876867

  Use a tally (or numbers) table. Here's a great article on them https://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/

  WITH

  E(n) AS(

  SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

  ),

  E2(n) AS(

  SELECT a.n FROM E a, E b

  ),

  E4(n) AS(

  SELECT a.n FROM E2 a, E2 b

  ),

  cteTally(n) AS(

  SELECT TOP 49 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 n

  FROM E4

  )

  SELECT (n*.25) - 6

  FROM cteTally;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  May 5, 2016 at 8:38 am

  #1876874

  Something like this perhaps (I'm just guessing on the rank)...

  -- Sample data

  DECLARE @table TABLE ([Year] int, Region varchar(20));

  INSERT @table VALUES (2013, 'South'), (2014, 'North'), (2015, 'South'), (2015, 'North');

  --Review:

  --SELECT * FROM @table;

  WITH

  E1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1))t(N)),

  iTally AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM E1 a, E1 b)

  SELECT [Year], Region, t = (-6 + (.25*N)), [rank] = DENSE_RANK() OVER (ORDER BY [year])

  FROM iTally

  CROSS JOIN @table

  ORDER BY [Year], Region, t-- ORDER BY Not required, including for display only, remove this for better performance

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001