34,591
社区成员
发帖
与我相关
我的任务
分享
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([column1] varchar(5),[column2] int)
insert [tb]
select 'kk',1 union all
select 'gg',1 union all
select 'erew',2 union all
select '43243',3
--sql2005
select distinct [column2],
stuff((select ','+[column1] from [tb] where b.[column2]=[column2] for xml path('')),1,1,'') [column1]
from [tb] b
--sql200
create function dbo.FC(@column2 int)
returns nvarchar(100)
as
begin
declare @sql nvarchar(100)
set @sql=''
select @sql=@sql+','+[column1] from [tb] where [column2]=@column2
return stuff(@sql,1,1,'')
end
select distinct dbo.FC([column2]) [column1],[column2] from [tb]
column1 column2
---------------------------------------------------------------------------------------------------- -----------
43243 3
erew 2
kk,gg 1
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-12 13:36:33
-- 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]([column1] varchar(5),[column2] int)
insert [tb]
select 'kk',1 union all
select 'gg',1 union all
select 'erew',2 union all
select '43243',3
--------------开始查询--------------------------
CREATE FUNCTION dbo.f_strUnite(@id varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + ltrim(column1) FROM tb WHERE column2=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt column1 = dbo.f_strUnite(column2),column2 FROM tb GROUP BY column2
drop table tb
drop function dbo.f_strUnite
go
----------------结果----------------------------
/* column1 column2
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
kk,gg 1
erew 2
43243 3
(3 行受影响)
*/
--SQL 2000
if object_id('test') is not null
drop table test
go
Create table test(Col1 varchar(12),Col2 int)
insert test values('kk',1)
insert test values('gg',1)
insert test values('erew',2)
insert test values('43243',3)
GO
--创建一个函数
Create function [dbo].[fn_test]
(@Col2 int)
returns varchar(20)
as
begin
declare @str varchar(20)
select @str=isnull(@str+',','')+Col1 from test where Col2=@Col2
return @str
end
GO
--查询
select Col1=dbo.fn_test(Col2),
col2
from test a
group by col2
/*
Col1 col2
----------- -----------
kk,gg 1
erew 2
43243 3
*/
--SQL 2005
declare @t table(Col1 varchar(12),col2 int)
insert @t values('kk',1)
insert @t values('gg',1)
insert @t values('erew',2)
insert @t values('43243',3)
select Col1=stuff((select ','+Col1 from @t where a.Col2=Col2 for xml path('')),1,1,''),
col2
from @t a
group by col2
/*
Col1 col2
----------- -----------
kk,gg 1
erew 2
43243 3
*/