--1、sql2000中只能用自定义的函数解决 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
我贴好了 ---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-11-04 11:23:15 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([FID] int,[Field1] varchar(1)) insert [tb] select 1,'A' union all select 1,'B' union all select 1,'C' union all select 2,'D' union all select 2,'E' union all select 2,'F' --------------开始查询-------------------------- select FID, [Field1]=stuff((select ','+[Field1] from tb t where FID=tb.FID for xml path('')), 1, 1, '') from tb group by fid ----------------结果---------------------------- /* FID Field1 ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 A,B,C 2 D,E,F(2 行受影响) */
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
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-04 11:23:15
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([FID] int,[Field1] varchar(1))
insert [tb]
select 1,'A' union all
select 1,'B' union all
select 1,'C' union all
select 2,'D' union all
select 2,'E' union all
select 2,'F'
--------------开始查询--------------------------
select FID, [Field1]=stuff((select ','+[Field1] from tb t where FID=tb.FID for xml path('')), 1, 1, '')
from tb
group by fid
----------------结果----------------------------
/* FID Field1
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 A,B,C
2 D,E,F(2 行受影响)
*/