求救：sqlserver里如何实现多行记录合并

-- =========================================

-- -----------t_mac 小编-------------

   ---希望有天成为大虾---- 

-- =========================================



IF OBJECT_ID('tb') IS NOT NULL

  DROP TABLE tb

GO

CREATE TABLE tb(名称 varchar(10),类型 varchar(10),编号 varchar(20))

go

insert into tb

select 

'广西',    '旱地',  '034/034' union all select 

'广西'  ,  '旱地'  ,'039/033' union all select 

'广西',    '菜地'  ,'034/039' union all select 

'湖北'  ,  '耕地' , '434/021' union all select 

'湖北'  ,  '耕地' , '3224/021' union all select 

'北京'   , '草地' , '4332/05' union all select 

'北京'   , '水库' , '132/051' union all select 

'北京'   , '水库' , '102/051' 



go

create function kkk1(@mingcheng varchar(10))

returns varchar(100)

as

begin 

  declare @s varchar(100)

  set @s=''

  select @s=@s+','+编号 from tb where 名称=@mingcheng

  return stuff(@s,1,1,'')

end

create function kkk2(@mingcheng varchar(10))

returns varchar(100)

as

begin 

  declare @ss varchar(100)

  set @ss=''

  select @ss=@ss+','+类型 from (select distinct 名称,类型 from tb ) t

  where 名称=@mingcheng   

  group by 类型

  return stuff(@ss,1,1,'') 

end

go

select 名称,dbo.kkk2(名称) as 类型,dbo.kkk1(名称) as 编号

from tb

group by 名称

/*

北京	草地,水库	4332/05,132/051,102/051

广西	菜地,旱地	034/034,039/033,034/039

湖北	耕地	434/021,3224/021

*/



/*------------



-------*/

--论坛很多 给你个例子

/****************************************************************************************************************************************************** 

合并分拆表数据 



整理人：中国风(Roy) 



日期:2008.06.06 

******************************************************************************************************************************************************/ 



--> --> (Roy)生成測試數據 



if not object_id('Tab') is null 

    drop table Tab 

Go 

Create table Tab([Col1] int,[Col2] nvarchar(1)) 

Insert Tab 

select 1,N'a' union all 

select 1,N'b' union all 

select 1,N'c' union all 

select 2,N'd' union all 

select 2,N'e' union all 

select 3,N'f' 

Go 



合并表: 



SQL2000用函数: 



go 

if object_id('F_Str') is not null 

    drop function F_Str 

go 

create function F_Str(@Col1 int) 

returns nvarchar(100) 

as 

begin 

    declare @S nvarchar(100) 

    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1 

    return @S 

end 

go 

Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab 



go 



SQL2005用XML: 



方法1: 



select 

    a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'') 

from 

    (select distinct COl1 from Tab) a 

Cross apply 

    (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b 



方法2: 



select 

    a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44)) 

from 

    (select distinct COl1 from Tab) a 

cross apply 

    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE) 

                .query(' <Tab> 

                {for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")} 

                {concat("",string(/Tab[last()]/@COl2))} 

                </Tab>') 

                )b 



SQL05用CTE: 



;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab) 

,Roy2 as 

(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 

union all 

select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1) 

select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0) 





生成结果: 

/* 

Col1        COl2 

----------- ------------ 

1          a,b,c 

2          d,e 

3          f 



(3 行受影响) 

*/