Solved

T-SQL Remove NULL values from query

Posted on 2014-01-09
15
1,213 Views
Last Modified: 2014-01-09
This is a continuation of question:  http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28334234.html

With the following SQL script:

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
group by t1.WBS1, t1.Org 

Open in new window


How may I adjust it so that there are no NULL values shown as records in the second discipline column?

The result of this comes more NULLs than anything:

12151      NULL
12177      NULL
12184      NULL
12185      NULL
12250      NULL
12289      NULL
12462      NULL
12485      NULL
12502      NULL
12508      NULL
13008      NULL
13065      NULL
13066      NULL
13076      NULL
13078      NULL
13128      NULL
13167      NULL
13173      NULL
13340      NULL
13360      NULL
13367      NULL
8236      ACOUSTICS
8238      ACOUSTICS
8254      ACOUSTICS
8277      ACOUSTICS
8351      ACOUSTICS
8354      ACOUSTICS
8362      ACOUSTICS
8363      ACOUSTICS
8379      ACOUSTICS
8392      ACOUSTICS
8406      ACOUSTICS
8430      ACOUSTICS

and I just want to not show NULLs in the results.
Thank you in advance!
0
Comment
Question by:stephenlecomptejr
  • 8
  • 5
15 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39768181
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 

Open in new window

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768238
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.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39768258
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 
                                            

Open in new window

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768316
Thank you Jim.   I didn't realize it till too late.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768318
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?
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768326
Also too Guy,

as I run the above query the values are not separated by commas now....
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39768333
for the "order by", just put ORDER BY WBS1 after the GROUP BY line
not sure why the "commas" would disappear ...
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768364
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 

Open in new window


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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39768394
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 

Open in new window

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768430
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
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39768654
ok, let's see (not tested):
select t1.WBS1      
 , stuff((select ', '+ sq.Label as "data()"
          from (select distinct t2.Label 
                 from PR x
		  join CFGOrgCodesDescriptions t2
            on x.Org like '%:' + t2.Code
              and t2.code not in ( 'RE', '00' )
           where x.WBS1 = t1.WBS1
                 )  sq
            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 
                                            

Open in new window

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768676
Thank you so much.

It works great!  You are fantastic.
0
 
LVL 1

Author Closing Comment

by:stephenlecomptejr
ID: 39768682
Appreciate it so much.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now