一.定义表变量
代码如下:
DECLARE @T1 table
(
UserID int ,
UserName nvarchar(50),
CityName nvarchar(50)
);
insert into @T1 (UserID,UserName,CityName) values (1,"a","上海")
insert into @T1 (UserID,UserName,CityName) values (2,"b","北京")
insert into @T1 (UserID,UserName,CityName) values (3,"c","上海")
insert into @T1 (UserID,UserName,CityName) values (4,"d","北京")
insert into @T1 (UserID,UserName,CityName) values (5,"e","上海")
select * from @T1
-----最优的方式
SELECT CityName,STUFF((SELECT "," + UserName FROM @T1 subTitle WHERE CityName=A.CityName FOR XML PATH("")),1, 1, "") AS A
FROM @T1 A
GROUP BY CityName
----第二种方式
SELECT B.CityName,LEFT(UserList,LEN(UserList)-1)
FROM (
SELECT CityName,(SELECT UserName+"," FROM @T1 WHERE CityName=A.CityName FOR XML PATH("")) AS UserList
FROM @T1 A
GROUP BY CityName
) B
stuff(select "," + fieldname from tablename for xml path("")),1,1,"")
这一整句的作用是将多行fieldname字段的内容串联起来，用逗号分隔。
for xml path是SQL Server 2005以后版本支持的一种生成XML的方式。
stuff函数的作用是去掉字符串最前面的逗号分隔符。