SQL Server2000和2005中合并列值

<!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} @font-face {font-family:"/@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:宋体; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-font-kerning:1.0pt;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} /* Page Definitions */ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

 

 

有表 tb, 如下：

id    value

—–——

1     aa

1     bb

2     aaa

2     bbb

2     ccc

 

需要得到结果：

id     values

—————–

1      aa, bb

2      aaa, bbb, ccc

 

在 SQL Server2000 中我们处理的方式一般都是用自定义函数去处理：

举例如下：

 

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, values = dbo. f_str( id) from tb group by id

 

在 SQL Server2005 中，这种统计有了一种新的方法，直接使用语句调用 sql2005 的函数实现：

举例如下：

 

– 示例数据

DECLARE @t TABLE ( id int , value varchar ( 10))

Insert @t Select 1, 'aa'

UNION ALL Select 1, 'bb'

UNION ALL Select 2, 'aaa'

UNION ALL Select 2, 'bbb'

UNION ALL Select 2, 'ccc'

 

– 查询处理

Select *

FROM (

Select DISTINCT

id

FROM @t

) A

OUTER APPLY(

Select [values]= STUFF ( REPLACE ( REPLACE (

(

Select value FROM @t N

Where id = A. id

FOR XML AUTO

), '<N value=''' , ',' ), '''/>' , '' ), 1, 1, '' )

) N

 

/*– 结果

id          values

———–—————-

1           aa,bb

2           aaa,bbb,ccc

 

(2 行受影响 )

–*/

 

最近从 CSDN 上又发现了一种方法！

declare @a varchar ( 100)

select @a= coalesce ( @a+ '/' , '' )+ PNAME from HPINF where perid like '207%'

select @a