SQL中的叠加--结果集合转换为字符串(小提示)
SQL中的叠加(小提示):有如下的需求,用SQL实现把结果集合转换为字符串.
如下:
可以有至少如下三种方法来做转换:
1. 在.NET中通常的做法是先取出结果集放在DataTable中,再利用foreach循环取出每个字段串联起来. 亦如这样,在SQL中也有相同的做法,就是用游标(CURSOR)做循环,示例代码如下:
declare @mytable table
(
Col1 varchar ( 10 )
)
insert into @mytable values
( ' AA ' ),
( ' BB ' ),
( ' CC ' )
declare mytype cursor local for
select SUBSTRING ( @Colstring , 2 , LEN ( @Colstring ) - 1 ) as Colstring
select Col1 from @mytable
open mytype
fetch next from mytype into @Col1
while @@FETCH_STATUS=0
begin
set @Colstring+=','+@Col1
fetch next from mytype into @Col1
end
deallocate mytypedeclare @Colstring varchar(50)=''
declare @Col1 varchar(10)
转换之后的字符串:
CURSOR在SQL中的效率很差,当数据量比较大时(>1 Million)会严重影响性能,不建议使用.
2. SQL查询中直接赋值,这种方式CODE比较简捷.
declare @mytable table
(
Col1 varchar ( 10 )
)
insert into @mytable values
( ' AA ' ),
( ' BB ' ),
( ' CC ' )declare @Colstring varchar(50)
select @Colstring = isnull(@Colstring + ',' , '' ) + isnull( Col1 , '' )
from @mytable
select @Colstring as Colstring
运行结果:
3. FOR XML PATH
首先转换为XML的数据库类型.
declare @mytable table
(
Col1 varchar ( 10 )
)
insert into @mytable values
( ' AA ' ),
( ' BB ' ),
( ' CC ' )
SELECT ' , ' + Col1 --No aliasFROM @mytable
ORDER BY Col1
FOR XML PATH( 'TYPE' )
可以看到输入结果是XML类型的,TYPE为节点
< TYPE > ,AA </ TYPE >
< TYPE > ,BB </ TYPE >
< TYPE > ,CC </ TYPE >
假如没有TYPE节点,就会接近想要的结果,修改CODE如下:
declare @mytable table
(
Col1 varchar ( 10 )
)
insert into @mytable values
( ' AA ' ),
( ' BB ' ),
( ' CC ' )
SELECT ' , ' + Col1 -- No alias
FROM @mytable
ORDER BY Col1
FOR XML PATH( '' )
太好了,只需要把前置逗号(,)取消就OK了,接续修改CODE:
declare @mytable table
(
Col1 varchar ( 10 )
)
insert into @mytable values
( ' AA ' ),
( ' BB ' ),
( ' CC ' )
select STUFF ((
SELECT ' , ' + Col1 -- No alias
FROM @mytable
ORDER BY Col1
FOR XML PATH( '' )) , 1 , 1 , space ( 0 )) as Colstring
得到需求的结果:
扩展如下:
declare @mytable table
(
id int ,
potype varchar ( 10 )
)
insert into @mytable values ( 1 , ' A>A ' )
insert into @mytable values ( 1 , ' B&B ' )
insert into @mytable values ( 1 , ' C<C ' )
SELECT p1.id,
STUFF ( ( SELECT ' , ' + potype
FROM @mytable p2
WHERE p2.id = p1.id
ORDER BY potype
FOR XML PATH( '' ),TYPE
).value( ' . ' , ' VARCHAR(MAX) ' ) , 1 , 1 , SPACE ( 0 )) AS Concat_Values
FROM @mytable p1
GROUP BY p1.id ;
DECLARE @mytable
TABLE (
id INTEGER NOT NULL ,
potype VARCHAR ( 10 ) NOT NULL
);
INSERT @mytable VALUES ( 1 , ' A>A ' );
INSERT @mytable VALUES ( 1 , ' B&B ' );
INSERT @mytable VALUES ( 1 , ' C<C ' );
SELECT P1.id,
csv = STUFF
(
(
SELECT ' , ' + P2.potype
FROM @mytable P2
WHERE P2.id = P1.id
ORDER BY P2.potype ASC
FOR XML PATH( '' ), TYPE
).value( ' ./text()[1] ' , ' VARCHAR(MAX) ' )
, 1 , 1 , SPACE ( 0 )
)
FROM @mytable P1
GROUP BY P1.id;
推荐采用第二种,或是第三种方法.
智能推荐
注意！
本站转载的文章为个人学习借鉴使用，本站对版权不负任何法律责任。如果侵犯了您的隐私权益，请联系我们删除。