将1列多行转换为字符串连接起来显示的查询语句 (很实用)

1. SQLServer   
   从2005版本后支持for xml path来实现将一列多行转换为字符串来显示， 使用方式如下：

 

create table test(UserID int, UserName nvarchar(50),CityName nvarchar(50)); 
insert into test (UserID,UserName,CityName) values(1,'a','上海'); 
insert into test (UserID,UserName,CityName) values(2,'b','北京'); 
insert into test (UserID,UserName,CityName) values(3,'c','上海'); 
insert into test (UserID,UserName,CityName) values(4,'d','北京'); 
insert into test (UserID,UserName,CityName) values(5,'e','上海'); 

select * from test; 
SELECT B.CityName, LEFT(UserList, LEN(UserList)-1)  as namecnt FROM( 
SELECT CityName, (SELECT UserName+',' FROM test WHERE CityName=A.CityName for XML PATH('')) AS UserList 
FROM test A  Group BY CityName 
) B 

 
结果如下: