sql - Dynamic PIVOT with Numbered Column Names -

i'm getting following when trying pivot table:

country  birmingham  dallas  new delhi --------------------------------------- india    null        null    new delhi uk       birmingham  null    null usa      null        dallas  null 

however, i'm trying (the total amount of distinct cities per country):

country city1        city2          city3 ---------------------------------------- india  new delhi     bangalore      hyderabad uk     london        birmingham     portsmouth usa    dallas        indianapolis   houston 

this code i'm using:

-- dynamic pivot declare @dynamicpivquery nvarchar(max) declare @colname nvarchar(max)  --get distinct values of pivot column select @colname =      coalesce(@colname + ',','') + quotename(city)     (select distinct city countries) b     order b.city     --prepare pivot query using dynamic set @dynamicpivquery =     n'select country, ' + @colname + '     countries     pivot (max(city)         city in (' + @colname + ')) piv'  exec sp_executesql @dynamicpivquery   table countries   country nvarchar(50)   city    nvarchar(50) 

i'm trying figure out if use like:

'city' + cast(row_number() on (partition country order country) varchar(10)) 

i using sql 2005 server database still in 2000 compatibility mode.

thank you

select country, [1] city1, [2] city2, [3] city3  (     select          country,          city,         row_number() over(partition country order country) seq     countries ) s pivot (     max(city)     seq in ([1], [2], [3]) ) p 

which results in:

country city1       city2           city3 india   new delhi   bangalore       hyderabad uk      london      birmingham      portsmouth usa     dallas      indianapolis    houston