Another solution using recursive cte - took me some time because I got bitten by integer math - had to ask my friend Denis Gobo to help.
;with cte as
(
select cName, nStartYear, nEndYear from @T
union all
select cName,
convert(Numeric(4,0),(convert(int,nStartYear) + 1)) as nStartYear,
nEndYear from cte where nStartYear < nEndYear
)
select cName, nStartYear as nYear from cte order by cName, nYear
>Hi All,
>I need help construct SQL Statement in T-SQL, which will convert single record with a range into multiple records. (MS SQL Server 2005)
>
>The source table has structure like:
>
>cName nChar(50)
>nStartYear numeric(4,0)
>nEndYear Numeric(4,0)
>
>cName nStartYear nEndYear
>Name1 2000 2003
>Name2 2010 2012
>Name3 2010 2010
>
>The resulting set should look like
>cName nYear
>Name1 2000
>Name1 2001
>Name1 2002
>Name1 2003
>Name2 2010
>Name2 2011
>Name2 2012
>Name3 2010
>
>Thank you in advance
>
>Yelena
If it's not broken, fix it until it is.
My Blog