SQL Server : 2 Pivot Questions -

all pivot examples saw till use aggregate functions. have no idea how can achieve following:

i have simple table test purposes:

;with cte ( select * (values (1, 'messi',    'graz'), (2, 'ronaldo',  'graz'), (3, 'weah',     'wien'), (4, 'nedved',   'wien'), (5, 'hagi',     'wien'), (6, 'puskas',   'istanbul'), (7, 'stoichkov', 'dubai'), (8, 'di baggio', 'dubai') ) t(cid, name, city)) 

i want achieve following:

1. to rotate table this:

   

2. and want rotate following query:

   select city, count(city) num_of_customers  customers  group city; 

which produces following result:

i want display this:

i never worked pivot tables until , grateful kind of help.

ps: table name customers.

both queries easy in dynamic fashion, nice if don't have fixed number of rows , need query adapt number of rows.

the first query:

declare @players varchar(max) select @players = stuff((select distinct ',['+cast(cid varchar(10))+']' customers xml path('')),1,1,'') declare @dynamic_pivot_query varchar(max)  set @dynamic_pivot_query = 'select '+@players+'   (select cid, name customers) s   pivot (max(name) cid in ('+@players+') ) p'  exec(@dynamic_pivot_query) 

result:

1       2       3       4       5       6       7           8 messi   ronaldo weah    nedved  hagi    puskas  stoichkov   di baggio 

the second query:

declare @cities varchar(max) select @cities = stuff((select distinct ',['+city+']' customers xml path('')),1,1,'')  declare @dynamic_pivot_query varchar(max) set @dynamic_pivot_query = 'select '+@cities+'     (select city customers) s    pivot (count(city) city in ('+@cities+') ) p'  exec(@dynamic_pivot_query) 

result:

dubai       graz        istanbul    wien ----------- ----------- ----------- ----------- 2           2           1           3