通过sql语句或存储过程怎样获取这样的结果？（在线等）

[indeed]

表中数据：
userType userId
WA319 yuansf
WB29 yuansf
WC1 yuansf

希望得到的结果

userType userId
WA319|WB29|WWB29 yuansf

谢谢各位大虾了！

[hellowork]

if object_id('tbTest') is not null
drop table tbTest
if object_id('fnMerge') is not null
drop function fnMerge
GO
create table tbTest(userType varchar(10),userId varchar(10))
insert tbTest
select 'WA319', 'yuansf' union all
select 'WB29', 'yuansf' union all
select 'WC1', 'yuansf'
GO
create function fnMerge(@userID varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '|' + userType from tbTest where userID = @userID
return stuff(@str,1,1,'')
end
GO

----查询
select dbo.fnMerge(userID) AS userType,userID from tbTest group by userID

drop table tbTest
drop function fnMerge

/*结果
userType userId
-----------------------------------------
WA319|WB29|WC1 yuansf
*/

[OracleRoob]

写一个聚合函数。

在SQL Server 2000中，常用的处理方式有两种：

1、写自定义聚合好函数，如上。
2、用游标处理。

用函数处理是最简单的实现方式。

[GEPIN]

2000 or 2005?

[hrb2008]

create table tb
(
userType varchar(5),
userId varchar(6)
)
insert into tb
select 'WA319','yuansf' union
select 'WB29','yuansf' union
select 'WC1','yuansf'
go

create function dbo.fun(@userid varchar(10))
returns varchar(50)
as
begin
declare @s varchar(50)
set @s=''
select @s=@s+'|'+userType from tb where userid=@userid
set @s=stuff(@s,1,1,'')
return @s
end
select distinct userId,dbo.fun(userId) userType from tb
userId userType
------ --------------------------------------------------
yuansf WA319|WB29|WC1

(1 行受影响)

[indeed]

二楼的大虾，请问用游标如何处理？

[millsyys2006]

create table B(userType varchar(10),userId varchar(10))
insert B
select 'WA319', 'yuansf' union all
select 'WB29', 'yuansf' union all
select 'WC1', 'yuansf'
GO
Create Function F_GetRole(@ID char(10))
Returns Nvarchar(2000)
As
Begin
Declare @S Nvarchar(2000)
Select @S = ''
Select @S = @S + '|' + UserType From B Where UserID = @ID
Select @S = Stuff(@S, 1, 1, '')
Return @S
End
GO
Select dbo.f_Getrole(userID) AS userType,userID
From B group by userid
GO
Drop Table B
Drop Function F_GetRole