I have a table with the following structure:

I would like to run an SQL query on the table that would return a 1 series for the FromAccount to ThruAccount with the Team and Location columns that belong to that series. I think this table would look something like this where a record is returned for each 1 value in the From Thru Span.

Can this be done in SQL Server?

CodePudding user response：

Assuming 101 was just missing due to a typo, we can use a recursive CTE to generate all the numbers between the lowest from and the highest to, then join to them.

DECLARE @min int, @max int;

SELECT @min = MIN(FromAccount), @max = MAX(ToAccount)
  FROM dbo.TableName;

WITH n(n) AS (SELECT @min UNION ALL
  SELECT n 1 FROM n WHERE n < @max
)
SELECT Account = n.n,
  t.Team, t.Location
FROM dbo.TableName AS t
INNER JOIN n
ON n.n BETWEEN t.FromAccount AND t.ToAccount
OPTION (MAXRECURSION 0);