/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu

Tuesday, August 21, 2012

SQL SERVER IN query between two comma separated column using split method

Split Method for split column value . . .


CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
        declare @idx int
        declare @slice varchar(8000)
   

        select @idx = 1
                if len(@String)<1 is="is" nbsp="nbsp" null="null" or="or" p="p" return="return" tring="tring">   

        while @idx!= 0
        begin
                set @idx = charindex(@Delimiter,@String)
                if @idx!=0
                        set @slice = left(@String,@idx - 1)
                else
                        set @slice = @String
               

                if(len(@slice)>0)
                        insert into @temptable(Items) values(@slice)


                set @String = right(@String,len(@String) - @idx)
                if len(@String) = 0 break
        end
return
end
__________________________________________________________________________


WITH testdata
(
CommaColumn,
I_AddContest_Id,
I_ModeratorID,
S_Moderator,
S_ContestTitle,
S_Contest_Image,
S_Brief_Desc,
I_Hierarchy_Detail_ID,
I_Hierarchy_Master_ID
)
AS
(

SELECT
(',' + cast(I_ModeratorID as varchar) +',' + S_ZoneID +','+ S_AreaId +','+ S_CityId +','+ S_CentreID +',') as [FK_I_Hierarchy_Detail_ID],
A.I_AddContest_Id,
A.I_ModeratorID,
A.S_Moderator,
A.S_ContestTitle,
A.S_Contest_Image,
A.S_Brief_Desc,
B.I_Hierarchy_Detail_ID,
B.I_Hierarchy_Master_ID
FROM PULSEMKTG.T_Contest_Master AS A
LEFT OUTER JOIN T_User_Hierarchy_Details AS B
ON B.I_Hierarchy_Master_ID = A.I_ModeratorID
WHERE
A.Active=1
AND A.Approve=0 AND B.I_Status <> 0
AND GETDATE() >= ISNULL(B.Dt_Valid_From,GETDATE())
AND GETDATE() <= ISNULL(B.Dt_Valid_To,GETDATE())
AND B.I_User_ID = @I_User_ID  
)

SELECT
MAX(I_AddContest_Id) AS I_AddContest_Id  
,MAX(I_ModeratorID) AS I_ModeratorID
,MAX(S_Moderator) AS S_Moderator
,MAX(S_ContestTitle) AS S_ContestTitle  
,MAX(S_Contest_Image) AS S_Contest_Image
,MAX(cast(S_Brief_Desc as varchar(max))) AS S_Brief_Desc
FROM
(
SELECT D.items AS SplitValue,* FROM testdata AS C
CROSS APPLY dbo.Split(C.CommaColumn,',') AS D
WHERE
C.I_Hierarchy_Detail_ID = D.items
OR
C.I_Hierarchy_Master_ID = C.I_ModeratorID
) AS E

GROUP BY E.I_AddContest_Id
ORDER BY E.I_AddContest_Id DESC


2 comments:

  1. It is my excellent satisfaction to check out your web page and have fun with your fantastic publish here. I like that very much.

    SQL training chandigarh

    ReplyDelete

My Blog List

  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी संतान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी शमशान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • Kumaon University Nainital B.Ed entrance exam test result 2012 - कुमाऊँ विश्वविधालय, नैनीताल (उत्तराखण्ड)
    10 years ago