在线求助: SqL实现多行值合并成一列

场景如下:
有一个表temp,数据如下所示： 
ID(key)       Code1    code2   code3            Name     
1             001       01       1              名称101 
2             001       01       2              名称102 
3             001       01       1              名称201 
4             003       02       3              名称301 

想得到以下的检索结果 
001         01     1    名称101；名称201
001         01     2    名称102 
003         02     3    名称301 


就是说只将temp表中间三列(甚至更多列)记录值相同的记录进行最后一列 Name 的记录合并


并且请教那种方法在数据库中的执行效率更高? 因为数据近W条!

10 个解决方案

#1

该回复于2011-02-19 10:19:35被版主删除

#2

该回复于2011-02-19 10:21:59被版主删除

#3

select Code1,Code2,Code3,
       [name]=stuff((select '；'+[name] from tb 
              where Code1=a.Code1 and Code2=a.Code2 and Code3=a.Code3 for xml path('')),1,1,'')
from tb a group by Code1,Code2,Code3

#4

[code=SQ]/*
标题：按某字段合并字符串之一(简单合并)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-06
地点：广东深圳

描述：将如下形式的数据按id字段合并value字段。
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc
需要得到结果：
id     value
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即：group by id, 求 value 的和（字符串相加）
*/
--1、sql2000中只能用自定义的函数解决
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go

create function dbo.f_str(@id varchar(10)) returns varchar(1000)
as
begin
  declare @str varchar(1000)
  select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id
  return @str
end
go

--调用函数
select id , value = dbo.f_str(id) from tb group by id

drop function dbo.f_str
drop table tb


--2、sql2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go

select id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by id

drop table tb


--3、使用游标合并数据
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
declare @t table(id int,value varchar(100))--定义结果集表变量
--定义游标并进行合并处理
declare my_cursor cursor local for
select id , value from tb
declare @id_old int , @id int , @value varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @value
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
    if @id = @id_old
       select @s = @s + ',' + cast(@value as varchar)
    else
      begin
        insert @t values(@id_old , stuff(@s,1,1,''))
        select @s = ',' + cast(@value as varchar) , @id_old = @id
      end
    fetch my_cursor into @id , @value
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursor

select * from @t
drop table tb[/code]

#5

--sql 2000用函数解决.

create table tb(ID int, Code1 varchar(10),code2 varchar(10),code3 int,Name nvarchar(10))
insert into tb values(1 ,'001', '01', 1 ,N'名称101')  
insert into tb values(2 ,'001', '01', 2 ,N'名称102')  
insert into tb values(3 ,'001', '01', 1 ,N'名称201')  
insert into tb values(4 ,'003', '02', 3 ,N'名称301')
go

create function dbo.f_str(@Code1 varchar(10) , @code2 varchar(10),@code3 int) returns varchar(1000)
as
begin
  declare @str varchar(1000)
  select @str = isnull(@str + ',' , '') + cast(Name as varchar) from tb where Code1 = @Code1 and Code2 = @Code2 and Code3 = @Code3
  return @str
end
go

--调用函数
select Code1,Code2,Code3 , Name = dbo.f_str(Code1,Code2,Code3) from tb group by Code1,Code2,Code3

drop function dbo.f_str
drop table tb

/*
Code1      Code2      Code3       Name           
---------- ---------- ----------- ---------------
001        01         1           名称101,名称201
001        01         2           名称102
003        02         3           名称301

（所影响的行数为 3 行）
*/


--sql 2005
create table tb(ID int, Code1 varchar(10),code2 varchar(10),code3 int,Name nvarchar(10))
insert into tb values(1 ,'001', '01', 1 ,N'名称101')  
insert into tb values(2 ,'001', '01', 2 ,N'名称102')  
insert into tb values(3 ,'001', '01', 1 ,N'名称201')  
insert into tb values(4 ,'003', '02', 3 ,N'名称301')
go

select Code1,Code2,Code3, name = stuff((select ',' + name from tb t where code1 = tb.code1 and code2 = tb.code2 and code3 = tb.code3 for xml path('')) , 1 , 1 , '')
from tb
group by Code1,Code2,Code3

drop table tb

/*
Code1      Code2      Code3       name
---------- ---------- ----------- ---------------
001        01         1           名称101,名称201
001        01         2           名称102
003        02         3           名称301

(3 行受影响)
*/

#6

就是列值合并，dawugui给的很详细了。

#7

我在sql express 2005 中执行 怎么有这个错误呢?
Incorrect syntax near '='.

引用 5 楼 dawugui 的回
复:

--sql 2000用函数解决.

create table tb(ID int, Code1 varchar(10),code2 varchar(10),code3 int,Name nvarchar(10))
insert into tb values(1 ,'001', '01', 1 ,N'名称101')  
insert into tb values(2 ,'001', '01……

#8

引用 7 楼 kv4000 的回复:

我在sql express 2005 中执行 怎么有这个错误呢?
Incorrect syntax near '='.

[Quote=引用 5 楼 dawugui 的回
复:]

--sql 2000用函数解决.

create table tb(ID int, Code1 varchar(10),code2 varchar(10),code3 int,Name nvarchar……

你用这段即可,目前CSDN回复出来的内容格式有问题.


create table tb(ID int, Code1 varchar(10),code2 varchar(10),code3 int,Name nvarchar(10))
insert into tb values(1 ,'001', '01', 1 ,N'名称101')   
insert into tb values(2 ,'001', '01', 2 ,N'名称102')   
insert into tb values(3 ,'001', '01', 1 ,N'名称201')   
insert into tb values(4 ,'003', '02', 3 ,N'名称301')
go

select Code1,Code2,Code3, name = stuff((select ',' + name from tb t where code1 = tb.code1 and code2 = tb.code2 and code3 = tb.code3 for xml path('')) , 1 , 1 , '')
from tb
group by Code1,Code2,Code3

drop table tb

#9

create table tb(ID int, Code1 varchar(10),code2 varchar(10),code3 int,Name nvarchar(10))
insert into tb values(1 ,'001', '01', 1 ,'名称101')   
insert into tb values(2 ,'001', '01', 2 ,'名称102')   
insert into tb values(3 ,'001', '01', 1 ,'名称201')   
insert into tb values(4 ,'003', '02', 3 ,'名称301')

select Code1,Code2,Code3,
  [name]=stuff((select '；'+[name] from tb  
  where Code1=a.Code1 and Code2=a.Code2 and Code3=a.Code3 for xml path('')),1,1,'')
from tb a group by Code1,Code2,Code3

/*
Code1      Code2      Code3       name
---------- ---------- ----------- -----------------
001        01         1           名称101；名称201
001        01         2           名称102
003        02         3           名称301 

#10

.....这些问题类型 看了好多年了