写一个函数USE [FPDTrade] GO /****** Object: UserDefinedFunction [dbo].[trade_GetSplitTable] Script Date: 11/17/2009 20:46:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER function [dbo].[trade_GetSplitTable] ( @str nvarchar(2000), --要分割的字符串 @pat nvarchar(30) ) returns @returntable table(id int identity(1,1),strn nvarchar(200)) as begin declare @patLength int set @patLength = len(@pat) set @str=rtrim(ltrim(@str)) declare @i int set @i=10 declare @tmpstr nvarchar(2000) set @tmpstr=@str while @i<>0 begin select @i = CHARINDEX(@pat,@tmpstr) if @i<>0 begin insert into @returntable(strn) values(SUBSTRING(@tmpstr,1,@i-1)) set @tmpstr = SUBSTRING(@tmpstr,@i+@patLength,len(@tmpstr)) end else Return end Return end
不好意思。发错拉USE [FPDTrade] GO /****** Object: UserDefinedFunction [dbo].[getAttrbyProductid] Script Date: 11/17/2009 20:47:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <> -- Create date: <2009-10-29> -- Description: <将供求属性竖表转横表取数据> -- ============================================= ALTER FUNCTION [dbo].[getAttrbyProductid] ( @pid uniqueidentifier, @n int ) RETURNS nvarchar(1000) AS BEGIN declare @str nvarchar(1000) set @str='' SELECT top(@n) @str=@str+',' + Attribute.AttributeName+':'+ ProductAttribute.AttributeValue+' '+ Attribute.Units FROM ProductAttribute INNER JOIN Attribute ON ProductAttribute.AttributeId = Attribute.AttributeId where ProductAttribute.ProductID = @pid order by Attribute.sort if(len(@str)>1) set @str=right(@str,len(@str)-1) return(@str) END 这才是
GO
/****** Object: UserDefinedFunction [dbo].[trade_GetSplitTable] Script Date: 11/17/2009 20:46:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[trade_GetSplitTable]
(
@str nvarchar(2000), --要分割的字符串
@pat nvarchar(30)
)
returns @returntable table(id int identity(1,1),strn nvarchar(200))
as
begin
declare @patLength int
set @patLength = len(@pat)
set @str=rtrim(ltrim(@str))
declare @i int
set @i=10
declare @tmpstr nvarchar(2000)
set @tmpstr=@str
while @i<>0
begin
select @i = CHARINDEX(@pat,@tmpstr)
if @i<>0
begin
insert into @returntable(strn) values(SUBSTRING(@tmpstr,1,@i-1))
set @tmpstr = SUBSTRING(@tmpstr,@i+@patLength,len(@tmpstr))
end
else Return
end
Return
end
GO
/****** Object: UserDefinedFunction [dbo].[getAttrbyProductid] Script Date: 11/17/2009 20:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <>
-- Create date: <2009-10-29>
-- Description: <将供求属性竖表转横表取数据>
-- =============================================
ALTER FUNCTION [dbo].[getAttrbyProductid]
(
@pid uniqueidentifier,
@n int
)
RETURNS nvarchar(1000)
AS
BEGIN
declare @str nvarchar(1000)
set @str=''
SELECT top(@n) @str=@str+',' + Attribute.AttributeName+':'+ ProductAttribute.AttributeValue+' '+ Attribute.Units
FROM ProductAttribute INNER JOIN Attribute ON ProductAttribute.AttributeId = Attribute.AttributeId
where ProductAttribute.ProductID = @pid
order by Attribute.sort
if(len(@str)>1)
set @str=right(@str,len(@str)-1)
return(@str)
END
这才是
1 a
2 bc
3 c
这样的。