---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-05-15 17:50:33 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) -- Jun 17 2011 00:54:03 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([id] int,[value] varchar(5)) insert [huang] select 1,'zhang' union all select 1,'wang' union all select 1,'li' --------------开始查询--------------------------SELECT a.[id] ,
STUFF(( SELECT ',' + [value] FROM [huang] b WHERE b.[id] = a.[id]
FOR XML PATH('') ), 1, 1, '') '[value]' FROM [huang] a GROUP BY a.[id] ----------------结果---------------------------- /* id [value] ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 zhang,wang,li */
Use tempdb; Go Select id,name Into tb From (Select 1 as id, 'zhang' as name Union all select 1 ,'wang' Union All select 1 ,'li' Union All select 2,'yang') as tt; go Declare @result as varchar(100) = ''; Go Create function dbo.f(@id int)returns varchar(200) as begin Declare @result varchar(200) = ''; Select @result = @result +name+',' From tb Where id = @id; return Left(@result,Len(@result)-1); endSelect id,dbo.f(id) as result From tb Group by Id
试试用 for xml pathselect B.id ,left(B.namelist,len(namelist)-1) as value from (select id,(select name+',' from test where A.id=id for xml path('')) as namelist from test A group by id) B
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-15 17:50:33
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[value] varchar(5))
insert [huang]
select 1,'zhang' union all
select 1,'wang' union all
select 1,'li'
--------------开始查询--------------------------SELECT a.[id] ,
STUFF(( SELECT ',' + [value]
FROM [huang] b
WHERE b.[id] = a.[id]
FOR
XML PATH('')
), 1, 1, '') '[value]'
FROM [huang] a
GROUP BY a.[id]
----------------结果----------------------------
/*
id [value]
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 zhang,wang,li
*/
Go
Select id,name Into tb
From (Select 1 as id, 'zhang' as name Union all
select 1 ,'wang' Union All
select 1 ,'li' Union All
select 2,'yang') as tt;
go
Declare @result as varchar(100) = '';
Go
Create function dbo.f(@id int)returns varchar(200)
as
begin
Declare @result varchar(200) = '';
Select @result = @result +name+','
From tb
Where id = @id;
return Left(@result,Len(@result)-1);
endSelect id,dbo.f(id) as result
From tb
Group by Id
(select id,(select name+',' from test where A.id=id for xml path('')) as namelist from test A group by id) B