May 5, 2008 at 4:20 am
Dear All,
I have written a function on passing of formid it will written the value with comma contacted.
Create FUNCTION dbo.OEServiceTypeList_fn
( @FormId int )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @TempOEDetails table
( ServiceTypeDetails varchar(1000) )
DECLARE @ServiceTypeList varchar(1000)
DECLARE @Temp VarChar(1000)
DECLARE @Temp1 VarChar(1000)
DECLARE @TempServiceTypeList varchar(1000)
SET @ServiceTypeList = ''
INSERT INTO @TempOEDetails
SELECT vw_OERequests.ServiceTypeName
FROM AgIndCoeStuffingPlanInfo
Inner Join vw_OERequests on AgIndCoeStuffingPlanInfo.FormId = vw_OERequests.StuffingId
WHERE AgIndCoeStuffingPlanInfo.FormId = @FormId
And vw_OERequests.StuffingId is not null
IF @@ROWCOUNT > 0
Select @Temp=CharIndex(@ServiceTypeList,ServiceTypeDetails) from @TempOEDetails
IF (@Temp=0)
BEGIN
UPDATE @TempOEDetails
SET @ServiceTypeList = ( @ServiceTypeList + ServiceTypeDetails + ', ' )
END
IF(len(@ServiceTypeList)>0)
BEGIN
Set @TempServiceTypeList= substring( @ServiceTypeList, 1, ( len( @ServiceTypeList ) - 1 ))
END
ELSE
BEGIN
SET @TempServiceTypeList = ''
END
RETURN @TempServiceTypeList
END
select formcode,dbo.OEServiceTypeList_fn(FormId) 'Service Types'
from agindcoestuffingplaninfo
where formid=23
OUTPUT:
Formcode Service Types
OESTUFF-23Consolidation, Consolidation, Consolidation, Consolidation, Consolidation, Consolidation
I want the output like
Formcode Service Types
OESTUFF-23Consolidation
May 5, 2008 at 5:19 am
Maybe make your select distinct?
SELECT DISTINCT vw_OERequests.ServiceTypeName
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy