字段连接

Question

有个表如下：
ID   item#
1     22
3     33    
4     44
6     22
6     55想得到下面的结果：
item#  allID
22      1,6
33      3
44      44
55      6也就是把item#所有的ＩＤ连接起来，组合成一个字段

Answer 1

--> 测试数据: Test
if object_id('Test') is not null drop table Test
create table Test (ID int,item# int)
insert into Test
select 1,22 union all
select 3,33 union all
select 4,44 union all
select 6,22 union all
select 6,55
go--> 2005的解决方法简单高效，使用2000的朋友略过此句：
select item#,ID=stuff((select ','+rtrim(ID) as [text()] from Test where item#=a.item# for xml path('')),1,1,'') from Test as a group by item#
/*
item#    ID
-------- --------
22       1,6
33       3
44       4
55       6
*/--> 2000创建字符串合并函数：fn_Test_ID
go
create function fn_Test_ID (@item# int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=isnull(@str+',','')+rtrim(ID) from Test where item#=@item#
return(@str)
end
go--> 调用自定义函数进行汇总：
select item#,ID=dbo.fn_Test_ID(item#) from Test group by item#
/*
item#    ID
-------- --------
22       1,6
33       3
44       4
55       6
*/--> 删除测试
drop function fn_Test_ID
drop table Test

Answer 2

合并列值
原著：邹建
改编：爱新觉罗.毓华  2007-12-16  广东深圳表结构，数据如下：
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc需要得到结果：
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即：group by id, 求 value 的和（字符串相加）1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--1. 创建处理函数
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')
goCREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value FROM tb WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO-- 调用函数
SELECt id, value = dbo.f_str(id) FROM tb GROUP BY iddrop table tb
drop function dbo.f_str/*
id          value      
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
（所影响的行数为 2 行）
*/--2、另外一种函数.
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 f_hb(@id int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(value as varchar) from tb where id = @id
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as value from tbdrop table tb
drop function dbo.f_hb/*
id          value      
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
（所影响的行数为 2 行）
*/2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
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 * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
        SELECT [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM tb N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb/*
id          values
----------- -----------
1           aa,bb
2           aaa,bbb,ccc(2 行受影响)
*/--SQL2005中的方法2(csdn dobear_0922 提供)
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')
goselect id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id/*
id          values
----------- --------------------
1           aa,bb
2           aaa,bbb,ccc(2 row(s) affected)*/drop table tb

Answer 3

--创建函数
create function f_temp(@item int)
returns varchar(1000)
as
begin
declare @result varchar(1000)
select @result=@result+','+ID from 表 where item#=@item
set @result=substring(@result,2,len(@result)-1)
return @result
end--调用函数
select distinct item#,dbo.f_temp(ID) allID from 表

Answer 4

--创建函数
create function f_temp(@item int)
returns varchar(1000)
as
begin
declare @result varchar(1000)
select @result=@result+','+ID from 表 where item#=@item
set @result=substring(@result,2,len(@result)-1)
return @result
end--调用函数
select distinct item#,dbo.f_temp(item#) allID from 表--应该传入item#  刚刚写错了

Answer 5

这种一般适合于用函数来完成
create function dbo.fn_Item(@itemNo as varchar(12))
returns varchar(100)
as 
begin
declare @itemName varchar(30)
        set @itemName=''
select @itemName=@itemName+name+',' from 表 where item_no=@itemNo
return (left(@itemName,len(@itemName)-1)
end
select item,allID=dbo.fn_Item(item)
from 表
group by item