列如有表TAB!：
|
1 2 3 4 5 6 |
A B 1 aa 2 bb 1 cc 2 dd 3 ff |
想要得到查询结果
|
1 2 3 |
1 aa,cc 2 bb,dd 3 ff |
|
1 2 3 4 5 6 7 8 9 10 |
-- 测试数据: #tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb (A int,B varchar(2)) insert into #tb select 1,'aa' union all select 2,'bb' union all select 1,'cc' union all select 2,'dd' union all select 3,'ff' |
实现方法
|
1 2 3 4 5 |
-- SQL Server 2005 select a ,stuff( (select ',' + b from #tb where a = t.a for xml path('') ), 1, 1, '' ) as b from #tb as t group by a; |
|
1 2 3 4 5 |
a b ------------------------------------------------------------------------------------------------------------------- 1 aa,cc 2 bb,dd 3 ff |
(3 行受影响)