求SQL语句，行变列的问题

[calfly]

id name department
1 A 1
2 B 1
3 C 2
4 D 2

怎样才能查询显示为：

department member
1 A B
2 C D

[子陌红尘]

如果member都放在一个字段中，如下：

----------------------------------------------------
--建自定义函数
create function f_str(@department int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+' '+rtrim(member)
from tabname where department=@department
return(stuff(@r,1,2,''))
end
go

--查询
select
department,
dbo.f_str(department) as member
from
tabname
group by
department

[daijingjie2002]

以上的函数要改一下，需要有标

[Softlee81307]

create table test18(
id int,
name varchar(1),
Department int
)
insert into test18
select 1, 'A', 1 union all
select 2, 'B', 1 union all
select 3, 'C', 2 union all
select 4 , 'D', 2

select * from test18
--------------------------------------------------------------------
---------------------Create function--------------------------
Create function gettest18(@d int)
returns varchar(1000)
as
begin
declare @p varchar(1000)
set @p=''
select @p=@p+name+' ' from test18 where department=@d
return(@p)
end
-----------------------------------the function end--------------------

---------------------下面是實現語句--------------
select department,member=dbo.gettest18(department) from test18 group by department
--------------結果--------------
department member
1 A B
2 C D

[vinsonshen]

这种用 类似 libin_ftsafe(的函数就能实现了