SQL列合并

-- =============================================================================
-- 標題: 合并列值
-- 整理: takako_mu
-- 时间: 2009-10-19
-- 地点: 昆山
-- =============================================================================
/*
表結構如下:
id    value 
----- ------ 
1    aa 
1    bb 
2    aaa 
2    bbb 
2    ccc 

結果如下:
id    values 
------ ----------- 
1      aa,bb 
2      aaa,bbb,ccc 
*/

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 

------------------------------------------------------------
--1. sql2000舊方法:利用函數
create 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 id
 
------------------------------------------------------------
--2. sql2000舊方法:另一個函數
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 tb 

------------------------------------------------------------
--3. sql2005:outer apply
select A.id,B.[newValues] from 
    (select distinct id from tb)A 
    outer apply
    (
        select [newValues]= stuff(replace(replace(
                (
                    select [value] from tb N
                    where id = A.id
                    for xml auto
                ), '<N value="', ','), '"/>', ''), 1, 1, '')
    )B

/*
注:    APPLY 是在一个查询的 FROM 子句中指定的新的关系运算符。
    它允许您对外部表的每一行调用表值函数，可选地使用外部表的列作为函数的参数。
    APPLY 运算符有两种形式：CROSS APPLY 和 OUTER APPLY。
    如果表值函数为其返回一个空集合的话，前者不返回外部表的行，而后者则返回一个 NULL 值的行而不是函数的列。*/

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