Group by 自定义函数 连接字符串的简单处理
常有这样的转化需求
A | 1 |
B | 2 |
A | 3 |
B | 4 |
A | 1,3 |
B | 2,4 |
简单是使用Group By +自定义函数处理
USE [pt]
GO
/****** 对象: UserDefinedFunction [dbo].[GetStr] 脚本日期: 04/18/2008 20:37:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION [dbo].[GetStr]
(
-- Add the parameters for the function here
@p1 nvarchar(1000)
)
RETURNS varchar(8000)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(8000)
set @Result= ''
select @Result=@Result+','+rtrim(frange) from employee
where fpreempcode=@p1
-- Add the T-SQL statements to compute the return value here
SELECT @Result =stuff(@Result,1,1,'')
-- Return the result of the function
RETURN @Result
END
GO
GO
/****** 对象: UserDefinedFunction [dbo].[GetStr] 脚本日期: 04/18/2008 20:37:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION [dbo].[GetStr]
(
-- Add the parameters for the function here
@p1 nvarchar(1000)
)
RETURNS varchar(8000)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(8000)
set @Result= ''
select @Result=@Result+','+rtrim(frange) from employee
where fpreempcode=@p1
-- Add the T-SQL statements to compute the return value here
SELECT @Result =stuff(@Result,1,1,'')
-- Return the result of the function
RETURN @Result
END
GO
这个问题的处理方式很多,这里只是说一种方式,这个主要是简单。
posted on 2008-04-18 20:49 fredli2005 阅读(2155) 评论(0) 编辑 收藏 举报