sql server 2008 - Rollup multiple values in a Column to seperate columns -

i have below table...

 id     phone     address  1        502      100 main  1        602      100 main  2        502      500 s main  3        444      201 n point  3        777      201 n point  4        111      999 south 

i see...

 id     phone1     phone2  phone3   address  1       502         602            100 main  2       502                        500 s main  3       444        777             201 n point  4       111                        999 south   

i can have more 2 phone#'s per id,i thinkin maybe pivot i'm not sure appreciative.

the following script give result want. first establishes maximum number of phone numbers id. builds string pivot columns using maximum number of phone numbers (of form '[phone 1], ..., [phone n]' n being maximum number). dynamic sql statement built using pivot columns executed.

create table #t(     id int,     phone int,     [address] varchar(256) );  insert #t     (id,phone,[address]) values     (1,502,'100 main'),     (1,602,'100 main'),     (2,502,'500 s main'),     (3,444,'201 n point'),     (3,777,'201 n point'),     (4,111,'999 south');  declare @maxphonecount int; select      @maxphonecount=max(pc.phonecount)     (         select             id,             count(*) phonecount                     #t         group             id     ) pc;  declare @phoneids varchar(8000); declare @i int=1; while @i<=@maxphonecount begin     set @phoneids=         case when @phoneids null              '[phone '+cast(@i varchar(2))+']'             else @phoneids+',[phone '+cast(@i varchar(2))+']'         end;     set @i=@i+1; end  declare @sql varchar(max); set @sql='     select         *             (             select                 id,                 phone,                 ''phone '' + cast(row_number() on (partition id order phone) varchar(2)) phonelabel,                 [address]                             #t         ) p         pivot(             max(phone)             phonelabel in ('+@phoneids+')         ) piv     order         id';  exec (@sql);  drop table #t; 

the result of script is:

id  address     phone 1 phone 2 1   100 main    502     602 2   500 s main  502     null 3   201 n point 444     777 4   999 south   111     null