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