将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 AGroup BY CityName ) B
结果如下:
http://dl.iteye.com/upload/attachment/466410/1a70aac5-b7f7-3d5e-9690-d46ea48b82bb.png
http://dl.iteye.com/upload/attachment/466416/06c2de88-fc9c-355c-bdb5-afd621565a37.png
页:
[1]