--合并多行的某一列值
--stuff:删除指定的字符,并在指定的起点处插入另一组字符。
--函数
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
广宋庆玲西壮族自治区,玉林市,玉州区
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
发表评论