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)AOUTER 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,bb2 aaa,bbb,ccc (2 行受影响)--*/
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-03-26 15:31:59 -- Verstion: -- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) -- May 26 2009 14:24:20 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([FName] varchar(4),[FAddr] varchar(4)) insert [tb] select '张三','北京' union all select '张三','南京' --------------开始查询-------------------------- select FName, [FAddr]=stuff((select '/'+[FAddr] from tb t where FName=tb.FName for xml path('')), 1, 1, '') from tb group by FName ----------------结果---------------------------- /* FName FAddr ----- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 张三 北京/南京(1 行受影响) */
---trydeclare @tb table (fname nvarchar(10),faddr nvarchar(10)) insert into @tb select '张三','北京' union all select '张三','南京' select fname,faddr=stuff((select '/'+faddr from @tb where a.fname=fname for xml path('')),1,1,'') from @tb a group by fname fname faddr ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 张三 北京/南京(1 行受影响)
create table tb(id int, value varchar(10)) insert into tb values(1, 'aa') insert into tb values(1, 'bb') insert into tb values(2, 'aaa') insert into tb values(2, 'bbb') insert into tb values(2, 'ccc') gocreate function dbo.f_str(@id int) returns varchar(100) as begin declare @str varchar(1000) set @str = '' select @str = @str + ',' + cast(value as varchar) from tb where id = @id set @str = right(@str , len(@str) - 1) return @str end go--调用函数 select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_str drop table tb
create table #tb(Fname varchar(6),FAddr varchar(10)) insert into #tb select '张三','北京' union all select '张三','南京'select Fname,Faddr=stuff((select '/'+FAddr from #tb where a.Fname=Fname for xml path('')),1,1,'') from #tb a group by FnameFname Faddr 张三 北京/南京
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-26 15:31:59
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([FName] varchar(4),[FAddr] varchar(4))
insert [tb]
select '张三','北京' union all
select '张三','南京'
--------------开始查询--------------------------
select
FName, [FAddr]=stuff((select '/'+[FAddr] from tb t where FName=tb.FName for xml path('')), 1, 1, '')
from
tb
group by
FName
----------------结果----------------------------
/* FName FAddr
----- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
张三 北京/南京(1 行受影响)
*/
---trydeclare @tb table (fname nvarchar(10),faddr nvarchar(10))
insert into @tb select '张三','北京'
union all select '张三','南京'
select fname,faddr=stuff((select '/'+faddr from @tb where a.fname=fname for xml path('')),1,1,'') from @tb a
group by fname
fname faddr
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
张三 北京/南京(1 行受影响)
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
gocreate function dbo.f_str(@id int) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return @str
end
go--调用函数
select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_str
drop table tb
insert into #tb
select '张三','北京' union all
select '张三','南京'select Fname,Faddr=stuff((select '/'+FAddr from #tb where a.Fname=Fname for xml path('')),1,1,'')
from #tb a
group by FnameFname Faddr
张三 北京/南京