SQL中的叠加--结果集合转换为字符串(小提示) 转

还是比较实用的功能，自己也记录一下。转自http://www.cnblogs.com/changbluesky/archive/2010/06/10/1753036.html

SQL中的叠加(小提示):有如下的需求,用SQL实现把结果集合转换为字符串.

如下:

可以有至少如下三种方法来做转换:

1. 在.NET中通常的做法是先取出结果集放在DataTable中,再利用foreach循环取出每个字段串联起来. 亦如这样,在SQL中也有相同的做法,就是用游标(CURSOR)做循环,示例代码如下:

Cursor

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->declare  @mytable table
(
Col1 varchar(10)
)

insert into @mytable values
('AA'),
('BB'),
('CC')














select SUBSTRING(@Colstring,2,LEN(@Colstring)-1) as Colstring
declare mytype cursor local for
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比较简捷.

代码

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->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的数据库类型.

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->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('TYPE')

可以看到输入结果是XML类型的,TYPE为节点

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><TYPE>,AA</TYPE>
<TYPE>,BB</TYPE>
<TYPE>,CC</TYPE>

假如没有TYPE节点,就会接近想要的结果,修改CODE如下:

代码

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->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:

FOR XML PATH

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->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

得到需求的结果:

扩展如下:

代码

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->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 ;

代码

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->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;

推荐采用第二种,或是第三种方法.