create table table1(T_Code char(2),T_name char(5)) insert into table1 values('1','A') insert into table1 values('1','B') insert into table1 values('1','C') insert into table1 values('2','A') insert into table1 values('2','E') insert into table1 values('2','F') insert into table1 values('3','D') insert into table1 values('3','E') insert into table1 values('3','f') --存储过程--1.创建一个合并的函数 alter function fmerg(@id char(1)) returns varchar(8000) as begin declare @str varchar(8000) set @str='' select @str=@str+ltrim(rtrim(T_name)) from table1 where T_code=@id --set @str=right(@str,len(@str)-1) return(@str) End go--调用自定义函数得到结果 select distinct T_code,dbo.fmerg(T_code) from table1
--建立测试表,加入测试数据 create table table1(T_Code char(2),T_name char(5)) insert into table1 values('1','A') insert into table1 values('1','B') insert into table1 values('1','C') insert into table1 values('2','A') insert into table1 values('2','E') insert into table1 values('2','F') insert into table1 values('3','D') insert into table1 values('3','E') insert into table1 values('3','f') --建立函数 ALTER FUNCTION GETSTR(@T_Code NVARCHAR(20)) RETURNS NVARCHAR(200) AS BEGIN DECLARE @str NVARCHAR(200)
SET @str='' SELECT @str = @str+LTRIM(RTRIM(T_Name)) FROM Table1 WHERE T_Code =@T_Code RETURN @str END调用: select T_Code,dbo.getstr(T_Code) from table1 group by T_Code结果: 2 AEF 3 DEf 1 ABC
create table table1(T_Code char(2),T_name char(5))
insert into table1 values('1','A')
insert into table1 values('1','B')
insert into table1 values('1','C')
insert into table1 values('2','A')
insert into table1 values('2','E')
insert into table1 values('2','F')
insert into table1 values('3','D')
insert into table1 values('3','E')
insert into table1 values('3','f')
--存储过程--1.创建一个合并的函数
alter function fmerg(@id char(1))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+ltrim(rtrim(T_name)) from table1 where T_code=@id
--set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct T_code,dbo.fmerg(T_code) from table1
create table table1(T_Code char(2),T_name char(5))
insert into table1 values('1','A')
insert into table1 values('1','B')
insert into table1 values('1','C')
insert into table1 values('2','A')
insert into table1 values('2','E')
insert into table1 values('2','F')
insert into table1 values('3','D')
insert into table1 values('3','E')
insert into table1 values('3','f') --建立函数
ALTER FUNCTION GETSTR(@T_Code NVARCHAR(20))
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @str NVARCHAR(200)
SET @str='' SELECT @str = @str+LTRIM(RTRIM(T_Name))
FROM Table1 WHERE T_Code =@T_Code RETURN @str
END调用:
select T_Code,dbo.getstr(T_Code) from table1 group by T_Code结果:
2 AEF
3 DEf
1 ABC