SQL Pivot Column that has multiple values for same column -

trying pivot table results may have multiple rows same value have data looks far.

nbr      person     test  33      barry.      prim 33      brian       sup 33      burke rt    1st 33      ray         add 33      jake        add 33      smith       add 

i'm trying pivot looks this:

nbr   prim    sup     1st       add   add2       add3  33   barry   brian   burke      ray   jake       smith 

this have far normal pivot doesn't work grab ones same value in test column

create table #testtbl(nbr int,name varchar(20),test varchar(10)) insert #testtbl select '33','barry','prim' union select '33','brian','sup' union select '33','burke','1st' union select '33','ray','add' union select '33','jake','add' union select '33','smith','add'   select * ( select *  #testtbl ) x  pivot(  max(name) test in ([prim],[sup],[1st],[add]) ) pivot1 

any appreciated. if not possible have columns output add add2 , add3 thats fine. whatever works.

you can modifying test value using window functions:

select * (select tt.name,              (test + (case when count(*) on (partition test) = 1                            ''                            else cast(row_number() on (partition test order (select null)) varchar(255))                       end)) test       testtbl tt      ) x pivot(  max(name) test in ([prim], [sup], [1st], [add1], [add2], [add3]) ) pivot1 

a sql fiddle here.