Filter Results From 'STUFF' Function-CodePudding

I need to filter the results from my STUFF function which are held in the column 'combined_ops'. I need to only see records that do not contain the word 'Transfer'. I've tried the classic WHERE combined_ops NOT LIKE '%transfer%', but does not work. See my code and results and please let me know where I went wrong. Thank You.

SELECT Job, STUFF((SELECT DISTINCT '  '   Operation_Service
                   FROM dbo.Job_Operation
                   WHERE dbo.Job_Operation.Job = dbo.Job.Job 
                   for xml path('')),1,2,'') AS combined_ops
FROM  dbo.Job
WHERE dbo.Job.Status = 'Active' 
AND   dbo.Job.Customer_PO = 'tmi stock'

enter image description here

CodePudding user response：

There are a few ways to do this.

You can put the FOR XML in a CROSS APPLY and filter afterwards

SELECT
  j.Job,
  jo.combined_ops
FROM  dbo.Job j
CROSS APPLY (SELECT
    STUFF((SELECT DISTINCT '  '   jo.Operation_Service
           FROM dbo.Job_Operation jo
           WHERE jo.Job = j.Job 
           for xml path(''), type
          ).value('text()[1]','nvarchar(max)'),1,2,'')
) AS jo(combined_ops)
WHERE j.Status = 'Active' 
  AND j.Customer_PO = 'tmi stock'
  AND jo.combined_ops NOT LIKE '%transfer%'

Or you can use HAVING and conditional aggregation

SELECT Job,
       STUFF((
          SELECT '  '   jo.Operation_Service
             (
               SELECT jo.Operation_Service
               FROM dbo.Job_Operation jo
               WHERE jo.Job = j.Job
               GROUP BY jo.Operation_Service
               HAVING COUNT(CASE WHEN jo.Operation_Service LIKE '%transfer%' THEN 1 END) = 0
               ) jo
          for xml path(''), type
         ).value('text()[1]','nvarchar(max)'),1,2,'') AS combined_ops
FROM  dbo.Job j
WHERE j.Status = 'Active' 
  AND j.Customer_PO = 'tmi stock'

Note the use of the syntax for xml path(''), type).value('text()[1]','nvarchar(max)') to unescape XML characters correctly.

Note also the use of table aliases rather than three part column names.