with tb (a,b) as( select 1,'name1' union select 2,'name2' ),tc(a,b)as( select 1,'d1' union select 1,'d2' union select 2,'d3' union select 2,'d4' ) select distinct *, stuff((select ','+b from tc where tb.a=tc.a for xml path('')),1,1,'') from tb
---------------------------------------------------------------- -- Author :DBA_Huanzj(發糞塗牆)-- Version: -- Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) -- Oct 19 2012 13:38:57 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) -- ---------------------------------------------------------------- --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([id] int,[name] varchar(5)) insert [A] select 1,'name1' union all select 2,'name2'--> 测试数据:[B] if object_id('[B]') is not null drop table [B] go create table [B]([id] int,[aid] int,[bname] varchar(3)) insert [B] select 1,1,'d1' union all select 2,1,'d2' union all select 3,2,'d3' union all select 4,2,'d4' --------------开始查询-------------------------- select a.id,a.name,b.bname from [A] a inner join ( select a.[aid], stuff((select ','+[bname] from [B] b where b.[aid]=a.[aid] for xml path('')),1,1,'') 'bname' from [B] a group by a.[aid])b on a.id=b.aid ----------------结果---------------------------- /* id name bname ----------- ----- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 name1 d1,d2 2 name2 d3,d4*/
create table 主表 (a int, b varchar(10))insert into 主表 select 1, 'name1' union all select 2, 'name2'
create table 子表 (a int,b int,c varchar(10))insert into 子表 select 1, 1, 'd1' union all select 2, 1, 'd2' union all select 3, 2, 'd3' union all select 4, 2, 'd4' create function fn(@a int) returns varchar(20) as begin declare @r varchar(20) select @r=isnull(@r,'')+','+c from 子表 where b=@a
return stuff(@r,1,1,'') end select a,b,dbo.fn(a) 'c' from 主表/* a b c ----------- ---------- -------------------- 1 name1 d1,d2 2 name2 d3,d4(2 row(s) affected) */
with tb (a,b) as(
select 1,'name1' union
select 2,'name2'
),tc(a,b)as(
select 1,'d1' union
select 1,'d2' union
select 2,'d3' union
select 2,'d4'
)
select distinct *,
stuff((select ','+b from tc where tb.a=tc.a for xml path('')),1,1,'') from tb
-- Author :DBA_Huanzj(發糞塗牆)-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
-- Oct 19 2012 13:38:57
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(5))
insert [A]
select 1,'name1' union all
select 2,'name2'--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[aid] int,[bname] varchar(3))
insert [B]
select 1,1,'d1' union all
select 2,1,'d2' union all
select 3,2,'d3' union all
select 4,2,'d4'
--------------开始查询--------------------------
select a.id,a.name,b.bname from [A] a inner join (
select a.[aid],
stuff((select ','+[bname] from [B] b
where b.[aid]=a.[aid]
for xml path('')),1,1,'') 'bname'
from [B] a
group by a.[aid])b on a.id=b.aid
----------------结果----------------------------
/*
id name bname
----------- ----- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 name1 d1,d2
2 name2 d3,d4*/
create table 主表
(a int, b varchar(10))insert into 主表
select 1, 'name1' union all
select 2, 'name2'
create table 子表
(a int,b int,c varchar(10))insert into 子表
select 1, 1, 'd1' union all
select 2, 1, 'd2' union all
select 3, 2, 'd3' union all
select 4, 2, 'd4'
create function fn(@a int)
returns varchar(20)
as
begin
declare @r varchar(20) select @r=isnull(@r,'')+','+c
from 子表
where b=@a
return stuff(@r,1,1,'')
end
select a,b,dbo.fn(a) 'c'
from 主表/*
a b c
----------- ---------- --------------------
1 name1 d1,d2
2 name2 d3,d4(2 row(s) affected)
*/