0

Please login or register to vote for this query.

(click on this box to dismiss)

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)

fork query

edited oct 14 13

created oct 14 13 anonymous