SQL 将多条记录合成为1条!
求大神指教如何将多条记录合成为一条.例如:
1 a
1 b
1 c
1 d
1 e
合成为:
1 abcde
求大神指点! sql
[解决办法]
create table ta(id int,name varchar(10))
insert into ta
select 1,'a'
union all select 1,'b'
union all select 1,'c'
union all select 1,'d'
union all select 1,'e'
select a.id,
(select ''+name from ta b
where b.id=a.id
for xml path('')) 'name'
from ta a
group by a.id
/*
idname
---------------------
1abcde
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-12 09:56:50
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[name] varchar(1))
insert [huang]
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 1,'d' union all
select 1,'e'
--------------开始查询--------------------------
select a.id,REPLACE(
stuff((select ','+name from [huang] b
where b.ID=a.ID
for xml path('')),1,1,''),',','') 'name'
from [huang] a
group by a.ID
----------------结果----------------------------
/*
id name
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 abcde
*/
create table test(id int,name varchar)
insert into test
select '1','a'
union all select '2','b'
union all select '3','c'
union all select '4','d'
union all select '5','e'
select * from test;
select id=1,name=stuff((select ','+name from test for xml path('')),1,1,'')
declare @sql varchar(100)
set @sql=''
select @sql=@sql+col from #a
select distinct id,@sql from #a
if object_id('test') is not null
drop table test
go
create table test(id int,name varchar)
insert into test
select 1,'a'
union all select 1,'b'
union all select 1,'c'
union all select 1,'d'
union all select 1,'e'
--1.通过xml path来实现
select distinct id,
(select '' + t2.name
from test t2
where t1.id = t2.id
for XML path('')
) as name
from test t1
/*
idname
1abcde
*/
--2.通过T-SQL来实现,在数据较多时性能较好,可能改写为函数
declare @str varchar(1000);
declare @id int;
set @str = '';
select @id = id,
@str = @str + name
from test
select @id as id,@str as name
/*
idname
1abcde
*/
--3.把第二种改写为函数
--drop function dbo.fn_mergeSTR
create function dbo.fn_mergeSTR(@id int,@split varchar(10))
returns varchar(300)
as
begin
declare @str varchar(300);
set @str = '';
select @str = @str + name + @split
from test
where id = @id
set @str = left(@str , len(@str) - LEN(@split) )
return @str --返回值
end
go
select distinct
id,
dbo.fn_mergeSTR(id,'') as name
from test
/*
idname
1abcde
*/
