Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs
declare @t table(Id int, tagNo int, TermNo int) insert @t values (1,1000,2), (2,1000,3), (3,1000,7), (4,1002,1), (5,1002,10) ;with a as ( select max(TermNo)-1 MaxTermNo, TagNo from @t group by TagNo ), b as ( select 1 TermNo, TagNo, MaxTermNo from a union all select TermNo+1, TagNo, MaxTermNo from b where TermNo < MaxTermNo ), c as ( select TermNo, TagNo from b except select TermNo, TagNo from @t ) select t.TagNo ,STUFF(( select ',' + cast(TermNo as varchar(9)) from c t1 where t1.TagNo = t.TagNo order by TermNo for xml path(''), type ).value('.', 'varchar(max)'), 1, 1, '') TermNo from c t group by t.TagNo option (maxrecursion 0)