T-SQL Remove NULL values from query

we check for the existance of a mapped record:

select t1.WBS1      
 , stuff((select ', '+ t2.Label as "data()"
          from table2 t2
         where t1.Org like '%:' + t2.Code
              and t2.code not in ( 'RE', '00' )
            for xml path('')), 1, 2, '')  Discipline
  from table1 t1
WHERE EXISTS( select ', '+ t2.Label as "data()"
          from table2 t2
         where t1.Org like '%:' + t2.Code
              and t2.code not in ( 'RE', '00' )
    )
group by t1.WBS1, t1.Org 

Select allOpen in new window

The only problem with that result is now the first column that shows with NULLs start as

NULL      INFO TECHNOLOGY
NULL      THEATER
NULL      INFO TECHNOLOGY
NULL      AUDIOVISUAL
NULL      ACCOUNTING
NULL      SECURITY
NULL      THEATER
NULL      ACOUSTICS
NULL      DATA CENTER
NULL      AUDIOVISUAL
NULL      DATA CENTER
NULL      INFO TECHNOLOGY
NULL      PROJECT MANAGEMENT
NULL      AUDIOVISUAL
NULL      SECURITY
NULL      DATA CENTER
NULL      AUDIOVISUAL
NULL      SECURITY
NULL      AUDIOVISUAL
NULL      MARKETING
NULL      AUDIOVISUAL
NULL      DATA CENTER
NULL      INFO TECHNOLOGY
NULL      ACOUSTICS
NULL      AUDIOVISUAL
NULL      ACOUSTICS
NULL      DATA CENTER
NULL      INFO TECHNOLOGY
NULL      SECURITY
NULL      THEATER
NULL      AUDIOVISUAL
NULL      INFO TECHNOLOGY
NULL      ACOUSTICS
20      ACOUSTICS
24      AUDIOVISUAL
27      ACOUSTICS
29      ACOUSTICS
33      AUDIOVISUAL
36      ACOUSTICS


I guess I need it to not show NULLs in the first column as well as the second.
Sorry about that.

that one should be simple:

select t1.WBS1      
 , stuff((select ', '+ t2.Label as "data()"
          from table2 t2
         where t1.Org like '%:' + t2.Code
              and t2.code not in ( 'RE', '00' )
            for xml path('')), 1, 2, '')  Discipline
  from table1 t1
WHERE t1.WBS1 is not null
   AND EXISTS( select ', '+ t2.Label as "data()"
          from table2 t2
         where t1.Org like '%:' + t2.Code
              and t2.code not in ( 'RE', '00' )
    )
group by t1.WBS1, t1.Org 
                                            

Select allOpen in new window

Thank you Jim.   I didn't realize it till too late.

Hey Guy,

Sorry for this but I couldn't see this until now its working.
How do I through in the syntax an order by to sort by WBS1?

Also too Guy,

as I run the above query the values are not separated by commas now....

for the "order by", just put ORDER BY WBS1 after the GROUP BY line
not sure why the "commas" would disappear ...

So let me show you the results to be clear on what I'm talking about in an attached image file.

Table1 is PR in my actual setup.  Table2 is CFGOrgCodesDescriptions.
Here is the code I'm using:

select t1.WBS1      
 , stuff((select ', '+ t2.Label as "data()"
          from CFGOrgCodesDescriptions t2
         where t1.Org like '%:' + t2.Code
              and t2.code not in ( 'RE', '00' )
            for xml path('')), 1, 2, '')  Discipline
  from PR t1
WHERE t1.WBS1 is not null
   AND EXISTS( select ', '+ t2.Label as "data()"
          from CFGOrgCodesDescriptions t2
         where t1.Org like '%:' + t2.Code
              and t2.code not in ( 'RE', '00' )
    )
group by t1.WBS1, t1.Org ORDER BY WBS1 

Select allOpen in new window

And please note the results in the image where duplicates are appearing.
Sample-Messup.png

amended and tested code:

select t1.WBS1      
 , stuff((select ', '+ t2.Label as "data()"
          from PR x
		  join CFGOrgCodesDescriptions t2
            on x.Org like '%:' + t2.Code
              and t2.code not in ( 'RE', '00' )
           where x.WBS1 = t1.WBS1
            for xml path('')), 1, 2, '')  
			Discipline
  from PR t1
WHERE t1.WBS1 is not null
   AND EXISTS( select null
          from PR  x
		  join CFGOrgCodesDescriptions t2
           on t1.Org like '%:' + t2.Code
              and t2.code not in ( 'RE', '00' )
		  where x.wbs1 = t1.wbs1
    )
group by t1.WBS1 
ORDER BY WBS1 

Select allOpen in new window

Guy this is great stuff.

But (I'm sorry to ask you this) - if let's say ACOUSTICS appears more than once - we don't want it to repeat for that WBS1 - we just want it the one time.

Please note attachment...
Sample-Messup2.png

Thank you so much.

It works great!  You are fantastic.

Appreciate it so much.