表原始数据如下：
A B C D E
A B C D E A1
A B C D E A2
A B C D E A3
B C B B B B1
B C B B B B2
------------------------------------
我要查询的结果如下：
A B D E A1,A2,A3
B C B B B B1,B2
查询的SQL如何写？
------解决方案--------------------
select A, B, C, D, E,
stuff((select ','+f from t
where t.a = tt.a and
t.b = tt.b and
t.c = tt.c and
t.d = tt.d and
t.e = tt.e
for xml path('')),1,1,'') F
from t tt
group by A, B, C, D, E
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-03-19 11:04:19
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] varchar(1),[B] varchar(1),[C] varchar(1),[D] varchar(1),[E] varchar(1),[col] varchar(2))
insert [tb]
select 'A','B','C','D','E','A1' union all
select 'A','B','C','D','E','A2' union all
select 'A','B','C','D','E','A3' union all
select 'B','C','B','B','B','B1' union all
select 'B','C','B','B','B','B2'
--------------开始查询--------------------------
go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@A VARCHAR(50))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col from Tb where A=@A
return @S
end
go
Select distinct a,b,c,d,Col1=dbo.F_Str(A) from Tb
go
----------------结果----------------------------
/* a b c d Col1
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------
A B C D A1,A2,A3
B C B B B1,B2
(2 行受影响)
*/
------解决方案--------------------
引用了上面版主的代码，少了一列，修改了一下:
适合sql server 2000：
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] varchar(1),[B] varchar(1),[C] varchar(1),[D] varchar(1),[E] varchar(1),[col] varchar(2))
insert [tb]
select 'A','B','C','D','E','A1' union all
select 'A','B','C','D','E','A2' union all
select 'A','B','C','D','E','A3' union all
select 'B','C','B','B','B','B1' union all
select 'B','C','B','B','B','B2'
--------------开始查询--------------------------
go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(
@A VARCHAR(50),@B VARCHAR(50),@C VARCHAR(50),@D VARCHAR(50),@E varchar(50))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col
from Tb where A=@A AND b=@B AND C=@C AND D=@D and E = @E
return @S
end
go
Select a,b,c,d,E,
Col1=dbo.F_Str(A,B,C,D,E)
from Tb
group by a,b,c,d,E