经典sql代码：合并多行的某一列值

--合并多行的某一列值
--stuff：删除指定的字符，并在指定的起点处插入另一组字符。
create table tb
(id int,col1 varchar(10))
go

insert tb select 1 , '曾祥展'  
insert tb select 1 , '学无止境' 
insert tb select 1 , 'ok'
insert tb select 2 , 'B'

drop table tb

--函数
create function StrLink(@id int)
returns varchar(8000)
as
begin 
declare @sql varchar(8000)     
set @sql='' 
select @sql=@sql+','+col1 from tb where id=@id 
set @sql=stuff(@sql,1,1,'') 
return @sql
end

drop function StrLink

select * from tb
select col=dbo.StrLink(1)

/*

id   col1
-----------
1    曾祥展
1    学无止境
1    ok
2    B

     col
------------------
曾祥展,学无止境,ok

*/
 
 declare @Temp TABLE([Col1] varchar(50), [Col2] varchar(100)) 
Insert @Temp  
Select 'a', 'asd' union 
Select 'a', 'rdf' union 
Select 'a', 'dmg' union 
Select 'b', 'kk' union 
Select 'b', 'useh' 
--第一种方法
select a.[Col1],stuff((select','+b.[Col2] from @Temp as b where a.[Col1] = b.[Col1] for xml path('')),1,1,'') as res 
from @Temp a 
group by a.[Col1] 

--或者
SELECT DISTINCT
[Col1],
[Col2] = SUBSTRING(( SELECT ', ' + [Col2] as [text()]
FROM @Temp t2
WHERE t2.Col1 = t1.Col1
FOR XML path(''), elements
), 2, 100
)
FROM @Temp t1

--结果:
          a asd,dmg,rdf
          b kk,useh

原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2010/01/03/1638169.html

您可能感兴趣的文章: