Changes Row to Columns (Transpose) in Sql Server 2005 using COALESCE

Sample table as Cities in rows is convert to single column

view plaincopy to clipboardprint?

1. State (Tablename)  
2. -----  
3. City (Columnname)  
4. -----  
5. Chennai  
6. Madurai  
7. Coimbatore  

view plaincopy to clipboardprint?

1. declare @retstr varchar(8000)  
2.  select Top 5 @retstr =  COALESCE(@retstr + ';','') + City   
3. from State  
4. print @retstr  

Result:

view plaincopy to clipboardprint?

1. Chennai;Madurai;Coimbatore