----測試環境 --------將同組的字段用逗號連接起來
create table a(id1 int,id2 varchar(50) )
insert into a
select 2, 1 union all
select 2 , 2 union all
select 2 , 3 union all
select 2 ,3 union all
select 2 , 5 union all
select 3 , 2 union all
select 3 , 3 union all
select 3, 5 union all
select 3 , 5
if exists (select 1 from sysobjects where name='combine' and xtype='fn')
drop Function combine
------編寫函數實現整合功能
create FUNCTION combine(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+id2 FROM a WHERE id1=@vchA
RETURN(substring(@r,2,8000))
END
GO
if exists (select 1 from sysobjects where name='combine' and xtype='fn')
drop Function combine
------編寫函數實現整合功能
create FUNCTION combine(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+id2 FROM a WHERE id1=@vchA
RETURN(substring(@r,2,8000))
END
GO
再用以下語句
select M.id,M.name,dbo.combine(M.degree) as Degree,dbo.combine(M.scholl) as School from (select a.id,a.name,b.degree,b.school from T1 a ,T2 b where a.id=b.id)M