Hi and welcome to the forums. It is generally preferred if you can post ddl and sample data in a consumable format. Since you are brand new around here I did this for you so you can see an example. You can take a look at the first link in my signature for all the best practices when posting questions.

create table #Something

(

Table_ID varchar(10),

Category_ID int,

Start_Date datetime,

End_Date datetime

)

insert #Something

select 'G700052', 1, '2011-01-01', '2015-12-31 00:00:00.000' union all

select 'G700063', 1, '2011-01-01', '2015-12-31 00:00:00.000' union all

select 'G700089', 1, '2012-01-01', '2016-12-31 00:00:00.000' union all

select 'G800154', 1, '2013-01-01', '2017-12-31 00:00:00.000' union all

select 'LU100022', 37, '2012-02-03', '2016-12-31 00:00:00.000'

You don't need a recursive cte or any kind of looping mechanism for this type of thing. You need to use a tally table. You can read more about the tally table here.

http://www.sqlservercentral.com/articles/62867/[/url]

I like to use a cte for a tally table because it is lightning fast.

Something like this.

WITH

E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

)

select *

from #Something s

join cteTally t on t.N >= YEAR(start_date) and t.N <= YEAR(End_Date)