我用的是Access数据库:
里面有一个表A无主键,假如有以下数据
id title
1 A
1 B
2 C
3 D
4 E
4 F
...
现在是需要把重复的行合并!合并后的表数据如下:
id title
1 A,B
2 C
3 D
4 E,F
怎么实现?谢谢大家!提供sql下执行也可!主要是我不知道怎么写sql语句!
里面有一个表A无主键,假如有以下数据
id title
1 A
1 B
2 C
3 D
4 E
4 F
...
现在是需要把重复的行合并!合并后的表数据如下:
id title
1 A,B
2 C
3 D
4 E,F
怎么实现?谢谢大家!提供sql下执行也可!主要是我不知道怎么写sql语句!
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'StrLink')
DROP FUNCTION dbo.StrLink
GO
/*hlq8210:2005-10-21 */
CREATE FUNCTION dbo.StrLink(@FieldId sysname)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @ColStr VARCHAR(8000)
SET @ColStr=''
SELECT @ColStr=@ColStr+','+ title FROM title WHERE id =@FieldId
IF LEN(@ColStr)>0
BEGIN
SET @ColStr=RIGHT(@ColStr,LEN(@ColStr)-1)
END
RETURN @ColStr
END
GO
create table tb
(
id int,
title varchar(10)
)
insert into tb(id,title) values(1,'A')
insert into tb(id,title) values(1,'B')
insert into tb(id,title) values(2,'C')
insert into tb(id,title) values(3,'D')
insert into tb(id,title) values(4,'E')
insert into tb(id,title) values(4,'F')
go
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' +title from tb where id= @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
goselect distinct id,dbo.f_hb(id) as title from tbdrop table tb
result:
id title
----------- --------
1 A,B
2 C
3 D
4 E,F(所影响的行数为 4 行)
CREATE FUNCTION dbo.StrLink(@FieldId sysname)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @ColStr VARCHAR(8000)
SET @ColStr=''
SELECT @ColStr=@ColStr+','+ title FROM A WHERE id =@FieldId
IF LEN(@ColStr)>0
BEGIN
SET @ColStr=RIGHT(@ColStr,LEN(@ColStr)-1)
END
RETURN @ColStr
END
GO
---测试
select id,dbo.StrLink(id) from a group by id
create table A(id int, title varchar(10))
insert A select 1, 'A'
union all select 1, 'B'
union all select 2, 'C'
union all select 3, 'D'
union all select 4, 'E'
union all select 4, 'F'select id, title=cast(title as varchar(100)) into #T from A order by iddeclare @id int, @title varchar(100)
update #T set
@title=case when id=@id then @title+','+title else title end,
@id=id,
title=@titleselect id, title=max(title) from #T group by id--result
id title
----------- ----------------------------------------------------------------------------------------------------
1 A,B
2 C
3 D
4 E,F(4 row(s) affected)
drop table #T
drop table A