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
结果如下: