[COLOR=green]/* create test data */[/color]
[COLOR=blue]create[/color] [COLOR=blue]table[/color] #t (nID [COLOR=blue]int[/color] null, ID [COLOR=blue]varchar[/color](3), pay_freq [COLOR=blue]varchar[/color](1))
[COLOR=blue]insert[/color] #t (ID, pay_freq)
[COLOR=blue]select[/color] [COLOR=red]'ABC'[/color], [COLOR=red]'A'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'MMM'[/color], [COLOR=red]'S'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'XYZ'[/color], [COLOR=red]'A'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'XYZ'[/color], [COLOR=red]'B'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'ABC'[/color], [COLOR=red]'W'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'XYZ'[/color], [COLOR=red]'W'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'DEF'[/color], [COLOR=red]'C'[/color]
[COLOR=green]/* create variable for different row values (yr, account #, etc...) */[/color]
[COLOR=blue]declare[/color] @cnt [COLOR=blue]int[/color]
[COLOR=blue]set[/color] @cnt = 1
[COLOR=blue]create[/color] [COLOR=blue]table[/color] #tloop (nID [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), ID [COLOR=blue]varchar[/color](3))
[COLOR=blue]insert[/color] #tloop
[COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color] ID [COLOR=blue]from[/color] #t
[COLOR=blue]while[/color] @cnt <= ([COLOR=blue]select[/color] [COLOR=#FF00FF]max[/color](nID) [COLOR=blue]from[/color] #tloop)
[COLOR=blue]begin[/color]
[COLOR=green]--this piece assigns 'ID numbers' that reset each year,
[/color] [COLOR=green]--to be used in ordering later result set
[/color]
[COLOR=blue]create[/color] [COLOR=blue]table[/color] #t2 (nID [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), ID [COLOR=blue]varchar[/color](3), pay_freq [COLOR=blue]varchar[/color](1))
[COLOR=blue]insert[/color] #t2
[COLOR=blue]select[/color] ID, pay_freq
[COLOR=blue]from[/color] #t
[COLOR=blue]where[/color] ID = ([COLOR=blue]select[/color] ID [COLOR=blue]from[/color] #tloop [COLOR=blue]where[/color] nID = @cnt)
[COLOR=green]--select * from #t2
[/color]
[COLOR=blue]update[/color] a
[COLOR=blue]set[/color] a.nID = b.nID
[COLOR=blue]from[/color] #t a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] #t2 b
[COLOR=blue]on[/color] a.ID = b.ID
and a.pay_freq = b.pay_freq
[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #t2
[COLOR=blue]set[/color] @cnt = @cnt + 1
[COLOR=blue]end[/color]
[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #tloop
[COLOR=blue]declare[/color] @strsql [COLOR=blue]varchar[/color](1000)
[COLOR=blue]declare[/color] @strsql2 [COLOR=blue]varchar[/color](1000)
[COLOR=blue]declare[/color] @i [COLOR=blue]int[/color], @i2 [COLOR=blue]int[/color]
[COLOR=blue]set[/color] @i = 0
[COLOR=blue]set[/color] @i2 = 66
[COLOR=green]/* 66 = ascii value for 'B' */[/color]
[COLOR=blue]set[/color] @strsql = [COLOR=red]'select distinct a.ID '[/color]
[COLOR=blue]set[/color] @strsql2 = [COLOR=red]' from #t a '[/color]
[COLOR=blue]while[/color] @i < ([COLOR=blue]select[/color] [COLOR=#FF00FF]max[/color](nID) [COLOR=blue]from[/color] #t)
[COLOR=blue]begin[/color]
[COLOR=blue]set[/color] @strsql = @strsql +[COLOR=red]', '[/color] + [COLOR=blue]char[/color](@i2) + [COLOR=red]'.Pay_Freq as PF'[/color] + [COLOR=#FF00FF]cast[/color](@i + 1 [COLOR=blue]as[/color] [COLOR=blue]varchar[/color]) + [COLOR=red]' '[/color]
[COLOR=blue]set[/color] @strsql2 = @strsql2 + [COLOR=red]'left join (select ID, Pay_Freq from #t where nID = '[/color]
+ [COLOR=#FF00FF]cast[/color](@i + 1 [COLOR=blue]as[/color] [COLOR=blue]varchar[/color]) + [COLOR=red]') '[/color] + [COLOR=blue]char[/color](@i2) + [COLOR=red]' on a.ID = '[/color] + [COLOR=blue]char[/color](@i2) + [COLOR=red]'.ID '[/color]
[COLOR=blue]set[/color] @i = @i + 1
[COLOR=blue]set[/color] @i2 = @i2 + 1
[COLOR=blue]end[/color]
[COLOR=green]--print (@strsql + @strsql2)
[/color][COLOR=blue]execute[/color] (@strsql + @strsql2)
[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #t