怎么查询将行数据连成字符

如何查询将行数据连成字符
表A
ID 姓名 年龄
1 王一 20
1 王二 22
2 张三 23
2 张四 24

查询 
ID 属性
1 王一|20$王二|22
2 张三|23$张四|24


[解决办法]
参考:

SQL code

/*标题：按某字段合并字符串之一(简单合并)作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)时间：2008-11-06地点：广东深圳描述：将如下形式的数据按id字段合并value字段。id    value----- ------1     aa1     bb2     aaa2     bbb2     ccc需要得到结果：id     value------ -----------1      aa,bb2      aaa,bbb,ccc即：group by id, 求 value 的和（字符串相加）*/--1、sql2000中只能用自定义的函数解决create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')gocreate function dbo.f_str(@id varchar(10)) returns varchar(1000)asbegin  declare @str varchar(1000)  select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id  return @strendgo--调用函数select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_strdrop table tb--2、sql2005中的方法create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')from tbgroup by iddrop table tb--3、使用游标合并数据create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')godeclare @t table(id int,value varchar(100))--定义结果集表变量--定义游标并进行合并处理declare my_cursor cursor local forselect id , value from tbdeclare @id_old int , @id int , @value varchar(10) , @s varchar(100)open my_cursorfetch my_cursor into @id , @valueselect @id_old = @id , @s=''while @@FETCH_STATUS = 0begin    if @id = @id_old       select @s = @s + ',' + cast(@value as varchar)    else      begin        insert @t values(@id_old , stuff(@s,1,1,''))        select @s = ',' + cast(@value as varchar) , @id_old = @id      end    fetch my_cursor into @id , @valueENDinsert @t values(@id_old , stuff(@s,1,1,''))close my_cursordeallocate my_cursorselect * from @tdrop table tb
[解决办法]
SQL code
--sql 2000create table A(ID int,姓名 varchar(20),年龄 int)insert into a values(1 ,'王一', 20)insert into a values(1 ,'王二', 22)insert into a values(2 ,'张三', 23)insert into a values(2 ,'张四', 24)gocreate function dbo.f_str(@id int) returns varchar(1000)asbegin  declare @str varchar(1000)  select @str = isnull(@str + '$' , '') + cast(姓名 as varchar) + '|' + cast(年龄 as varchar) from a where id = @id  return @strendgo--调用函数select id , 属性 = dbo.f_str(id) from a group by iddrop function dbo.f_strdrop table a/*id          属性              ----------- ------------------1           王一|20$王二|222           张三|23$张四|24（所影响的行数为 2 行）*/
[解决办法]
--sql 2005
SQL code
create table A(ID int,姓名 nvarchar(20),年龄 int)insert into a values(1 ,N'王一', 20)insert into a values(1 ,N'王二', 22)insert into a values(2 ,N'张三', 23)insert into a values(2 ,N'张四', 24)goselect id, 属性 = stuff((select '$' + cast(姓名 as nvarchar) + '|' + cast(年龄 as nvarchar) from a t where id = a.id for xml path('')) , 1 , 1 , '')from agroup by iddrop table a/*id          属性----------- ------------------1           王一|20$王二|222           张三|23$张四|24(2 行受影响)*/
[解决办法]
SQL code
--> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([ID] int,[姓名] varchar(4),[年龄] int)insert [tbl]select 1,'王一',20 union allselect 1,'王二',22 union allselect 2,'张三',23 union allselect 2,'张四',24select ID,stuff((select '$'+[姓名]+'|'+LTRIM([年龄]) from tbl where ID=a.ID for xml path('')),1,1,'') as infofrom tbl a group by ID/*ID    info1    王一|20$王二|222    张三|23$张四|24*/