求SQL,把几条记录中的某个字段组合成一个逗号分隔的字符串

Question

表 [T1]
-----------------------------
字段 [id]  [name]
-----------------------------
      1     aaaa
      2     bbbb
-----------------------------
表 [T2]
-----------------------------
字段 [id]  [parentId]
-----------------------------
      50    1
      100   1
      350   2
      400   2
-----------------------------希望查询得到如下记录集：
-----------------------------
     [id]  [name]  [childrens]
-----------------------------
      1     aaaa    50,100
      2     bbbb    350,400
-----------------------------

Answer 1

--生成测试数据
create table 表(部门 int,人员 varchar(20))
insert into 表 select 1,'张三'
insert into 表 select 1,'李四'
insert into 表 select 1,'王五'
insert into 表 select 2,'赵六'
insert into 表 select 2,'邓七'
insert into 表 select 2,'刘八'
go--创建用户定义函数
create function f_str(@department int)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+','+人员 from 表 where 部门 = @department
    set @ret = stuff(@ret,1,1,'')
    return @ret 
end
go
--执行
select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门
go--输出结果
/*
部门  人员
----  --------------
1     张三,李四,王五
2     赵六,邓七,刘八
*/
--删除测试数据
drop function f_str
drop table 表
go

Answer 2

use pubs
if exists(select * from sysobjects where name='T1') drop table T1
if exists(select * from sysobjects where name='T2') drop table T2--------------------------------------------------
--测试用数据
create table T1(ID int,[name] varchar(10))
insert T1 select 1,'aaaa'
union all select 2,'bbbb'create table T2(id varchar(10),parentID int)
insert T2 select 50,1
union all select 100,1
union all select 350,1
union all select 400,2select * from T1
select * from T2
GO--编写用户自定义函数
create function fc_Tstr(@id int)
returns varchar(10)
as
begin
declare @vTmp varchar(10)
set @vTmp=''
select @vTmp=@vTmp+','+cast(id as varchar(10))  from T2 where parentID=@id
return stuff(@vTmp,1,1,'')
end 
GO--得到结果集的select
select id,[name],childrens=dbo.fc_Tstr(id)
from T1--删除临时数据
DROP TABLE T1,T2
DROP FUNCTION fc_Tstr

Answer 3

把以上代码贴到查析分析器中运行,可得到如下你想要的结果集,如下:
-----------------------------------------------------------------------------------------------------------id          name       childrens  
----------- ---------- ---------- 
1           aaaa       50,100,35
2           bbbb       400（所影响的行数为 2 行）

Answer 4

晕,结果怎么不对?.....哦,没错没错..
上面测试数据中,把1,aaaa中打多了1条(3条),关于2,bbbb的打少了1条...难怪事,写了半天,还以为不对呢...嘿嘿!

Answer 5

更正:
use pubs
if exists(select * from sysobjects where name='T1') drop table T1
if exists(select * from sysobjects where name='T2') drop table T2--------------------------------------------------
--测试用数据
create table T1(ID int,[name] varchar(10))
insert T1 select 1,'aaaa'
union all select 2,'bbbb'create table T2(id varchar(10),parentID int)
insert T2 select 50,1
union all select 100,1
union all select 350,2
union all select 400,2select * from T1
select * from T2
GO--编写用户自定义函数
create function fc_Tstr(@id int)
returns varchar(10)
as
begin
declare @vTmp varchar(10)
set @vTmp=''
select @vTmp=@vTmp+','+cast(id as varchar(10))  from T2 where parentID=@id
return stuff(@vTmp,1,1,'')
end 
GO--得到结果集的select
select id,[name],childrens=dbo.fc_Tstr(id)
from T1--删除临时数据
DROP TABLE T1,T2
DROP FUNCTION fc_Tstr
--得到的结果为:
id          name       childrens  
----------- ---------- ---------- 
1           aaaa       50,100
2           bbbb       350,400（所影响的行数为 2 行）

Answer 6

create table T1
(
  id int ,
  name varchar(500)
)insert into t1
select 1 , 'aaaa' union
select 2 , 'bbbb'create table t2
(
  id  int,
  parentid int
)insert into t2
select 50,  1 union
select 100, 1 union
select 350, 2 union
select 400, 2select id , name ,dbo.f_union3(id) 
from t1
create function f_union3(@id int)
returns varchar(8000)
as
  begin 
   declare @ret varchar(8000)
   set @ret = ''
   select @ret = @ret + ',' + cast(id as varchar)
   from T2 where parentid = @id
  
   set @ret = stuff(@ret , 1 , 1 , '')
   return @ret
  end

Answer 7

--SQL Server 2005 的解决办法:SELECT [id]=A.ID,
       [name]=A.NAME,
       BID=DBO.SQL_Aggregate(B.ID)
FROM T1 A INNER JOIN T2 B
ON A.ID=B.parentId
GROUP BY A.ID,A.NAME
--结果:id          name       BID
----------- ---------- -------------
1           aaaa       50,100
2           bbbb       350,400(2 行受影响)