CODEWITHSUNDEEP
sqlsql-servert-sql
haku
haku

Reputation: 4505

How to break a range to rows for each unit?

My input table looks like:

CREATE TABLE #Products (ProductName VARCHAR(100), StartYear INT, EndYear INT NULL)
INSERT INTO #Products (ProductName, StartYear, EndYear)
VALUES ('football', 2013, null)
,('Eggs', 2010, 2014)

I want to break the range into a row of its own. If EndYear is null, I would want to continue until the current year. My desired outcome looks like:

CREATE TABLE #DesiredOutput (ProductName VARCHAR(100), [Year] INT)
INSERT INTO #DesiredOutput (ProductName, Year)
VALUES ('football', 2013),
('football', 2014),
('football', 2015),
('football', 2016),
('football', 2017),
('Eggs', 2010),
('Eggs', 2011),
('Eggs', 2012),
('Eggs', 2013), 
('Eggs', 2014)

SELECT * 
FROM #DesiredOutput AS do

I cant even come up with way to get started to solve this in sql. With C#, I can think how to solve this but I would appreciate some pointers on how to do this in sql.

Upvotes: 1

Views: 40

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31889

Here is a Tally Table version:

WITH E1(N) AS(
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), 
CteTally(N) AS(
    SELECT TOP(SELECT MAX(EndYear) - MIN(StartYear) + 1 FROM #Products) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
)
SELECT
    p.ProductName,
    [Year] = p.StartYear + (t.N - 1)
FROM CteTally t
CROSS JOIN #Products p
WHERE
    p.StartYear + (N - 1) <= ISNULL(p.EndYear, YEAR(GETDATE()))
ORDER BY p.ProductName, [Year]

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82020

With the help of a Cross Apply and an ad-hoc tally table. If you have a number/tally table, that would do the trick as well.

Example

Select A.ProductName
      ,Year = B.N
 from #Products A
 Cross Apply (
                Select Top (IsNull(EndYear,Year(GetDate()))-StartYear+1)
                       N=StartYear-1+Row_Number() Over (Order By (Select NULL)) 
                  From master..spt_values n1
             ) B

Returns

ProductName Year
football    2013
football    2014
football    2015
football    2016
football    2017
Eggs        2010
Eggs        2011
Eggs        2012
Eggs        2013
Eggs        2014

Upvotes: 2

Related Questions