-- =============================================================================
-- 標題: 合并列值
-- 整理: 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
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