Hafifi Ali Hafifi Ali - 3 years ago 167
SQL Question

Filter out item in view files using STUFF

this is my view files. I need to filter out the joint_list to joint_list NOT IN('0'). Can anyone help me how to filter out it. Here is the output for the codes. view files

SELECT CUSTID,SPAID,STUFF((SELECT ', ' + CAST(RTrim(user1) AS VARCHAR(Max)) [text()]
FROM xtcwt_JointAcc(nolock)
WHERE CUSTID = t.custid and SPAID = t.SPAID and Status='A'
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') joint_list
FROM xtcwt_JointAcc t with(nolock)
GROUP BY CUSTID,SPAID


Thank You!!

Answer Source

put your query inside sub-select/CTE and filter the joint_list outside

SELECT *
FROM   (SELECT CUSTID,
               SPAID,
               Stuff((SELECT ', ' + Cast(Rtrim(user1) AS VARCHAR(Max)) [text()]
                      FROM   xtcwt_JointAcc(nolock)
                      WHERE  CUSTID = t.custid
                             AND SPAID = t.SPAID
                             AND Status = 'A'
                      FOR XML PATH(''), TYPE) .value('.', 'NVARCHAR(MAX)'), 1, 2, ' ') joint_list
        FROM   xtcwt_JointAcc t WITH(nolock)
        GROUP  BY CUSTID,
                  SPAID) a
WHERE  joint_list <> '0' 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download