--生成测试数据
create table test(id int,num varchar(10),string varchar(10))
insert into test values(1,'G123','abc')
insert into test values(2,'H456','abc')
insert into test values(3,'G123','abc')
insert into test values(4,'K789','xyz')
insert into test values(5,'H456','xyz')
insert into test values(6,'K789','xyz')
go--创建用户定义函数
create function f_str(@num varchar(10))
returns varchar(40)
as
begin
declare @ret varchar(40)
set @ret = ''
select @ret = @ret+','+string from (select distinct string from test where num = @num) t
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select num,dbo.f_str(num) as string from test group by num order by num
go--输出结果
/*
num string
---------- ----------------------------------------
G123 abc
H456 abc,xyz
K789 xyz
*/
--删除测试数据
drop function f_str
drop table test
go
create table test(id int,num varchar(10),string varchar(10))
insert into test values(1,'G123','abc')
insert into test values(2,'H456','abc')
insert into test values(3,'G123','abc')
insert into test values(4,'K789','xyz')
insert into test values(5,'H456','xyz')
insert into test values(6,'K789','xyz')
go--创建用户定义函数
create function f_str(@num varchar(10))
returns varchar(40)
as
begin
declare @ret varchar(40)
set @ret = ''
select @ret = @ret+','+string from (select distinct string from test where num = @num) t
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select num,dbo.f_str(num) as string from test group by num order by num
go--输出结果
/*
num string
---------- ----------------------------------------
G123 abc
H456 abc,xyz
K789 xyz
*/
--删除测试数据
drop function f_str
drop table test
go
(
ID INT,
Num VARCHAR(20),
STRING VARCHAR(20)
)INSERT INTO TSELECT 1,'G123','abc' UNION ALL
SELECT 2,'H456','abc' UNION ALL
SELECT 3,'G123','abc' UNION ALL
SELECT 4,'K789','xyz' UNION ALL
SELECT 5,'H456','xyz' UNION ALL
SELECT 6,'K789','xyz' GO
CREATE FUNCTION F_UNION
(
@Num VARCHAR(20)
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @re VARCHAR(200)
SET @re = '' SELECT @re = @re + ',' + STRING FROM T WHERE Num = @Num GROUP BY Num,STRING RETURN (STUFF(@re,1,1,''))
END
GOCREATE PROC P_UNION
AS
BEGIN
SELECT ID = IDENTITY(INT,1,1),Num, STRING=DBO.F_UNION(Num)
INTO #
FROM
T GROUP BY Num
SELECT * FROM #
DROP TABLE #
END
GO
--执行语句
EXEC DBO.P_UNION
--删除环境
DROP TABLE T
DROP FUNCTION F_UNION
DROP PROC P_UNION
1 BBB 1 abc
2 abc 1 xyz
3 abc 2 ABC
4 2 BBC
5 3 ccc
4 ddd
我想把 表2 中id與 表1 相同的string字符串合并成一条记录,string字段里的字符串合并中间用“,”号隔开,更新到 表1 中,
如 表1
id string
1 abc,xyz
2 ABC,BBC
3 ccc
4 ddd
这应该怎么实现呢
--建立测试环境
CREATE TABLE T01
(
ID INT,
STRING VARCHAR(20)
)CREATE TABLE T02
(
ID INT,
STRING VARCHAR(20)
)INSERT INTO T02SELECT 1,'abc' UNION ALL
SELECT 1,'xyz' UNION ALL
SELECT 2,'abc' UNION ALL
SELECT 2,'bbc' UNION ALL
SELECT 3,'ccc' UNION ALL
SELECT 4,'ddd' GOINSERT INTO T01
SELECT 1,'BBB' UNION ALL
SELECT 2,'abc' UNION ALL
SELECT 3,'abc' UNION ALL
SELECT 4,'' UNION ALL
SELECT 5,'333'
go--建立合并函数
CREATE FUNCTION F_UNION
(
@id VARCHAR(20)
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @re VARCHAR(200)
SET @re = '' SELECT @re = @re + ',' + STRING FROM T02 WHERE id = @id GROUP BY STRING RETURN (STUFF(@re,1,1,''))
END
GO--执行更新
UPDATE A SET STRING = B.STRING
FROM t01 A,(select ID,STRING = dbo.f_union(id) from t02 group by id) B
WHERE A.id = b.id
--查询
SELECT * FROM t01
--删除条件
drop function f_union
drop table t01
drop table t02
ID STRING
----------- --------------------
1 abc,xyz
2 abc,bbc
3 ccc
4 ddd
5 333(所影响的行数为 5 行)