表結構如下:
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
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