[求助] sqlserver2005中如何累加字符串。。急？

Question

比如这样：
name        orderNo
--------------------
chen        ORD1
CHEN        ORD2
CHEN        ORD3
WANG        ORD4
WANG        ORD5
WANG        ORD6
WANG        ORD7最后得到结果：
name           order
-----------------------------------
chen           ORD1,ORD2,ORD3
WANG           ORD4,ORD5,ORD6,ORD7用SQL语句可以做到吗？
谢谢

Answer 1

SELECT * FROM(SELECT DISTINCT name FROM tb)A OUTER APPLY( 
        SELECT [orderno]= STUFF(REPLACE(REPLACE( 
            ( 
                SELECT orderno FROM tb N 
                WHERE name = A.name
                FOR XML AUTO 
            ), ' <N orderno="', ','), '"/>', ''), 1, 1, '') 
)N 
drop table tb 

Answer 2

if object_id('[T]') is not null drop table [T]
create table [T] (name varchar(4),orderNo varchar(4))
insert into [T]
select 'chen','ORD1' union all
select 'CHEN','ORD2' union all
select 'CHEN','ORD3' union all
select 'WANG','ORD4' union all
select 'WANG','ORD5' union all
select 'WANG','ORD6' union all
select 'WANG','ORD7'
select distinct name,
stuff((select ','+orderNo from T where T.name=t1.name for xml path('')),1,1,'')
from T T1name 
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
chen ORD1,ORD2,ORD3
WANG ORD4,ORD5,ORD6,ORD7(2 行受影响)

Answer 3

if not object_id('tb') is null
drop table tb
Go
Create table tb([name] nvarchar(4),[orderNo] nvarchar(4))
Insert tb
select N'chen',N'ORD1' union all
select N'CHEN',N'ORD2' union all
select N'CHEN',N'ORD3' union all
select N'WANG',N'ORD4' union all
select N'WANG',N'ORD5' union all
select N'WANG',N'ORD6' union all
select N'WANG',N'ORD7'
Go
Select name,
       orderno=stuff((select ','+name
                      from tb
                      where name=t.name
                      for xml path('')),1,1,'') 
from tb t
group by name
/*
name orderno
---- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
chen chen,CHEN,CHEN
WANG WANG,WANG,WANG,WANG(2 個資料列受到影響)
*/

Answer 4

select name, [values]=stuff((select ','+orderno from tb t where name=tb.name for xml path('')), 1, 1, '') 
from tb 
group by name 
/*name values
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
chen ORD1,ORD2,ORD3
WANG ORD4,ORD5,ORD6,ORD7(2 行受影响)*/

Answer 5

create table #ta(name varchar(20),orderNo varchar(20))
insert #ta select 'chen' ,'ORD1'
insert #ta select 'CHEN' ,'ORD2'
insert #ta select 'CHEN' ,'ORD3'
insert #ta select 'WANG' ,'ORD4'
insert #ta select 'WANG' ,'ORD5'
insert #ta select 'WANG' ,'ORD6'
insert #ta select 'WANG' ,'ORD7'select name,STUFF((select ','+orderNO from #ta where a.name=name for XML path('')),1,1,'')  as orderNo
from #ta a
group by name
name                 orderNo
-------------------- -------------------------
chen                 ORD1,ORD2,ORD3
WANG                 ORD4,ORD5,ORD6,ORD7(2 行受影响)

Answer 6

use PracticeDB
if object_id('[TB]') is not null 
drop table [TB]
create table [TB](name varchar(5),orderno varchar(5))
insert [TB]
select 'chen', 'ORD1' union all
select 'CHEN', 'ORD2' union all
select 'CHEN', 'ORD3' union all
select 'WANG', 'ORD4' union all
select 'WANG', 'ORD5' union all
select 'WANG', 'ORD6' union all
select 'WANG', 'ORD7'
select name ,stuff((select ','+orderno
from tb
where name=a.name for xml path('')),1,1,'')as [order]
from tb a
group by namename order
chen ORD1,ORD2,ORD3
WANG ORD4,ORD5,ORD6,ORD7

Answer 7

学习中   能否请楼上的说说 
select ','+orderno from tb where name=a.name for xml path('')  中 path('')的意思？

Answer 8

用函数也很好
--测试
if object_id('tb') is not null     drop table tb
Go
Create table tb([name] varchar(4),[orderNo] varchar(4))
Insert tb
select 'che','ORD1' union all
select 'CHE','ORD2' union all
select 'CHE','ORD3' union all
select 'WANG','ORD4' union all
select 'WANG','ORD5' union all
select 'WANG','ORD6' union all
select 'WANG','ORD7'
Goif object_id('f_str') is not null     drop function f_str
Go 
create function dbo.f_str(@name varchar(4)) returns varchar(80)
as
begin
    declare @str varchar(100)
    set @str = ''
    select @str = @str + ',' + cast(orderNo as varchar) from tb where name = @name
    set @str = right(@str , len(@str) - 1)
    return @str
end
go--调用函数
select name , orderNo = dbo.f_str(name) from tb group by name--结果
/*（所影响的行数为 7 行）name orderNo                                                                          
---- -------------------------------------------------------------------------------- 
che  ORD1,ORD2,ORD3
WANG ORD4,ORD5,ORD6,ORD7（所影响的行数为 2 行）
*/

Answer 9

学习，函数的一千就会，但是XML的学下