查询两张表，拼接其中某字段值

Question

现在有2张表，father，son。他们是一对多关系。
father:(id,name)
1,王爸爸
2,张爸爸
son:(name,father_id)
大王子,1
二王子,1
三王子,1
大张子,2
二张子,2
现在需要实现输出如下
王爸爸    大王子，二王子，三网子
张爸爸    大张子，二张子
谢谢

Answer 1

--创建数据开始
if(object_id('father')is not null) drop table father
go
create table father
(
id int,
[name] varchar(50)
)
go
insert into father
select 1,'王爸爸' union
select 2,'张爸爸'
go
if(object_id('son')is not null) drop table son
go
create table son
(
[name] varchar(50),
father_id int
)
go
insert into son
select '大王子',1 union all
select '二王子',1 union all
select '三王子',1 union all
select '大张子',2 union all
select '二张子',2
go
--创建数据结束
--创建函数
create function getSonName()
returns @GetName table(fatherName varchar(50),sonName varchar(6000))
as
 begin
  declare @fatherID int
  declare @fatherName varchar(50)
  declare @SonName1 varchar(6000)
  declare Cfather cursor for select id,[name] from father
  open Cfather
  fetch next from Cfather into @fatherID,@fatherName
  while(@@fetch_status=0)
   begin
    set @sonName1=''
    select @sonName1 = isnull(@sonName1+',',' ')+ [name] from son where father_id = @fatherid
    insert into @Getname values(@fatherName,right(@sonName1,len(@sonName1)-1))
    fetch next from Cfather into @fatherID,@fatherName
   end
  close Cfather
  deallocate Cfather
  return
 end
go
--调用函数
 select * from GetSonName()
--结果展示
/*
fatherName                                         sonName
-------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
王爸爸                                                大王子,二王子,三王子
张爸爸                                                大张子,二张子(2 行受影响)*/

Answer 2

-----------补充
drop table father
go
drop table son
go
create table father(
id int not null,
name  varchar(30))
go
create table son(
father_id int not null,
name  varchar(30))
goinsert father
select 1,'A'
union all
select 2,'B'
go
insert son
select 1,'a1'
union all
select 1,'a2'
union all
select 1,'a3'
union all
select 2,'b1'
union all
select 2,'b2'
go可以输出：
| A | a1,a2,13 |
| B | b1,b2    |

Answer 3

--插入数据
insert father 
select 1,'A'union all
select 2,'B'union all
select 1,'A'union all
select 2,'B'
go 
insert son 
select 'a1',1union all
select 'a2',1union all
select 'a3',1union all
select 'b1',2union all
select 'b2',2
go 
--father 表信息
select * from father
/*
id          name
----------- --------------------------------------------------
1           王爸爸
2           张爸爸
1           A
2           B
1           A
2           B(6 行受影响)*/--son 表信息
select * from son /*
name                                               father_id
-------------------------------------------------- -----------
大王子                                                1
二王子                                                1
三王子                                                1
大张子                                                2
二张子                                                2
a1                                                 1
a2                                                 1
a3                                                 1
b1                                                 2
b2                                                 2(10 行受影响)
*/
--调用函数，函数在一楼已经创建，
select * from GetSonName() 
--结果展示
/*
fatherName                                         sonName
-------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
王爸爸                                                大王子,二王子,三王子,a1,a2,a3
张爸爸                                                大张子,二张子,b1,b2
A                                                  大王子,二王子,三王子,a1,a2,a3
B                                                  大张子,二张子,b1,b2
A                                                  大王子,二王子,三王子,a1,a2,a3
B                                                  大张子,二张子,b1,b2(6 行受影响)*/

Answer 4

谢谢你这么快的回复啊。我后来的补充是在看到你的回复之前写的，只是想别人回答的时候可以直接复制建表代码，就省的自己再建表，插数据了。
一直在看你的那个函数，游标的使用一直不太好。还想问你一下
1、这样的问题是不是必须使用游标？
2、可不可以实现这样的函数
getSonName(father_id int) return sonname varchar(600)
即，通过father_id,直接返回son_name：大王子,二王子,三王子 字符串。

Answer 5

----------------------------------------------------------------
-- Author  :TravyLee(物是人非事事休，欲语泪先流！)
-- Date    :2012-11-11 19:00:20
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
-- Jul  9 2008 14:43:34 
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据：[father]
if object_id('[father]') is not null 
drop table [father]
go 
create table [father]
(
[id] int,
[name] varchar(6)
)
insert [father]
select 1,'王爸爸' union all
select 2,'张爸爸'
--> 测试数据：[son]
if object_id('[son]') is not null 
drop table [son]
go 
create table [son]
(
[name] varchar(6),
[father_id] int
)
insert [son]
select '大王子',1 union all
select '二王子',1 union all
select '三王子',1 union all
select '大张子',2 union all
select '二张子',2
;with t
as(
select 
f.name,
s.name as Son_name
from 
[father] f
inner join
[son] s
on 
f.id=s.father_id
)
select
distinct name,
Son_name=stuff((select ','+Son_name
                    from t b 
                    where b.name=t.name for xml path('')), 1, 1, '')
from t
/*(2 行受影响)(5 行受影响)
name   Son_name
------ --------------------------
王爸爸    大王子,二王子,三王子
张爸爸    大张子,二张子(2 行受影响)
*/

Answer 6

感觉sql很是博大精深啊，看你们写的，很多都没见过，不好意思问你们基础的，所以自己还要网上找这些写法的意思。

Answer 7

你那样写是可以的,为什么我这样写就不行了?select 
distinct f.name as name,
son_name=stuff(
(select ','+son.name from son join father on
son.father_id=father.id for xml path('')),1,1,'')
from father fname son_name
A a1,a2,a3,b1,b2
B a1,a2,a3,b1,b2

Answer 8

可以，如果是通过fatherID,返回的话，就不需要游标了。
如果是返回完整的，就需要游标。