--合并多行的某一列值
--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))
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
广西壮族自治区,玉林市,玉州区
create index ix_id on 中国(id) create index ix_pid on 中国(pid) -- update dbo.中国 set allname= dbo.GetAllName(id) -- select dbo.GetAllName(450902) create function GetAllName (@id int) returns varchar(8000) as begin declare @sql varchar(8000) set @sql=''; with T(flag, id, pid, [name]) as (select @id as flag, id, pid, [name] from 中国 a where 1=1 and a.id=@id union all select @id as flag, b.id, b.pid, b.[name] from 中国 b inner join T c on c.pid=b.id) -- SELECT * FROM T WHERE pid !=0 select @sql=@sql+','+[name] from T where id!=1 order by id set @sql=stuff(@sql, 1, 1, '') return @sql end
本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2010/01/03/1638169.html，如需转载请自行联系原作者
