CODEWITHSUNDEEP
sql-serversql-server-2008temp-tablesstuff
Kapil
Kapil

Reputation: 1931

SQL Server STUFF query with the SAME table

I need to get the result like this:

I need to fetch the result query as like below

I have tried using this query:

SELECT
    STUFF((SELECT  ', ' + CONVERT(VARCHAR(50), RuleNumber)
           FROM #tempSelectPlusReferralsExtracts v2
           WHERE v2.RuleApprovedDate IN
                 (CASE WHEN (v2.RuleApprovedDate IS NULL ) 
                    THEN NULL
                    ELSE v2.RuleApprovedDate
                    END
                  )
           FOR XML PATH('')), 1, 2, '') [Rules], 
    *  
FROM
    #tempSelectPlusReferralsExtracts

My result looks like this:

enter image description here

Upvotes: -2

Views: 810

Answers (1)

Thom A
Thom A

Reputation: 95989

As I mentioned, seems your CASE expression should be around your string aggregation, not within it:

SELECT SPRE.RuleNumber,
       SPRE.RuleApprovedDate,
       CASE WHEN SPRE.RuleApprovedDate IS NULL THEN SPRE.RuleNumber
            ELSE STUFF((SELECT ', ' + CONVERT(varchar(50), v2.RuleNumber)
                        FROM #tempSelectPlusReferralsExtracts v2
                        FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,2,'')
       END AS [Rules]
FROM #tempSelectPlusReferralsExtracts SPRE;

Upvotes: 1

Related Questions