有个表XMKJ,有三个值XMMC, XH, KJH,如:
XMMC XH KJH
XM1 1 K1
XM1 2 K2
XM1 3 K3
XM2 1 K4
XM3 1 K5
XM3 2 K6
想实现按照相同字段值的XMMC按XH排序并合并KJH,结果为
XM1 K1;K2;K3
XM2 K4
XM3 K5;K6上网搜索,发现有人问过这样的问题,用函数方法可以解决我的函数如下:
create function GetXMKJH(@XMMC varchar)
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+';'+KJDJH
from XMKJ
where XMMC=@XMMC
set @str=right(@str,len(@str)-1)
return @str
end
函数创建完成后,运行下面语句
select distinct(xmmc), dbo.GetXMKJH(xmmc) from XMKJ
出现如下错误:
消息 536,级别 16,状态 2,第 1 行
传递到 RIGHT 函数的长度参数无效。
XMMC XH KJH
XM1 1 K1
XM1 2 K2
XM1 3 K3
XM2 1 K4
XM3 1 K5
XM3 2 K6
想实现按照相同字段值的XMMC按XH排序并合并KJH,结果为
XM1 K1;K2;K3
XM2 K4
XM3 K5;K6上网搜索,发现有人问过这样的问题,用函数方法可以解决我的函数如下:
create function GetXMKJH(@XMMC varchar)
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+';'+KJDJH
from XMKJ
where XMMC=@XMMC
set @str=right(@str,len(@str)-1)
return @str
end
函数创建完成后,运行下面语句
select distinct(xmmc), dbo.GetXMKJH(xmmc) from XMKJ
出现如下错误:
消息 536,级别 16,状态 2,第 1 行
传递到 RIGHT 函数的长度参数无效。
returns varchar(2000)
as
begin
declare @str varchar(2000) select @str=ISNULL(@str+';','')+KJDJH
from XMKJ
where XMMC=@XMMC
return @str
end
returns varchar(2000)
as
begin
declare @str varchar(2000) select @str=ISNULL(@str+';','')+KJDJH
from XMKJ
where XMMC=@XMMC
return @str
end
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+';'+KJDJH
from XMKJ
where XMMC=@XMMC
set @str=left(@str,len(@str)-1)
return @str
end
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-10 16:03:30
-- 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]([XMMC] varchar(3),[XH] int,[KJH] varchar(2))
insert [tb]
select 'XM1',1,'K1' union all
select 'XM1',2,'K2' union all
select 'XM1',3,'K3' union all
select 'XM2',1,'K4' union all
select 'XM3',1,'K5' union all
select 'XM3',2,'K6'
--------------开始查询--------------------------
CREATE FUNCTION dbo.f_strUnite(@id varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ':' + KJH FROM tb WHERE XMMC=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt XMMC, KJH = dbo.f_strUnite(XMMC) FROM tb GROUP BY XMMC
drop table tb
drop function dbo.f_strUnite
go
----------------结果----------------------------
/* XMMC KJH
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
XM1 K1:K2:K3
XM2 K4
XM3 K5:K6(3 行受影响)*/
create function GetXMKJH(@XMMC varchar(100))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+';'+KJDJH
from XMKJ
where XMMC=@XMMC
set @str=right(@str,len(@str)-1)
return @str
end