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

iteye_10076

于 2011-04-19 11:01:33 发布

阅读量141

点赞数

CC 4.0 BY-SA版权

分类专栏： Database 文章标签： XML C C++ C#

本文链接：https://blog.youkuaiyun.com/iteye_10076/article/details/82046468

Database 专栏收录该内容

2 篇文章

订阅专栏

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

结果如下: