How to remove nulls from result

Carlton Patterson 21 Reputation points
2021-02-06T12:26:45.15+00:00

Hello Community,

Can someone take tweak my query to remove the NULLs. I appreciate there are multiple ways to achieve this result without using case statements, but I would just like to know if its possible to just tweak the query to remove NULLs.

The data is as follows:

CREATE TABLE employee (  
    id int,  
    first_name varchar(50),  
    last_name varchar(50),  
    age int,  
    sex varchar(50),  
    employee_title varchar(50),  
    department varchar(50),  
    salary int,  
    target int,  
    bonus int,  
    email varchar(50),  
    city varchar(50),  
    address varchar(50),  
    manager_id int)  
  
INSERT employee VALUES  
(1,'Allen','Wang',55,'F','Manager','Management',200000,0,300,'Allen@company.com','California','23St',1),  
(13,'Katty','Bond',56,'F','Manager','Management',150000,0,300,'Katty@company.com','Arizona','',1),  
(19,'George','Joe',50,'M','Manager','Management',100000,0,300,'George@company.com','Florida','26St',1),  
(11,'Richerd','Gear',57,'M','Manager','Management',250000,0,300,'Richerd@company.com','Alabama','',1),  
(10,'Jennifer','Dion',34,'F','Sales','Sales',100000,200,150,'Jennifer@company.com','Alabama','',13),  
(18,'Laila','Mark',26,'F','Sales','Sales',100000,200,150,'Laila@company.com','Florida','23St',11),  
(20,'Sarrah','Bicky',31,'F','Senior Sales','Sales',200000,200,150,'Sarrah@company.com','Florida','53St',19),  
(21,'Suzan','Lee',34,'F','Sales','Sales',130000,200,150,'Suzan@company.com','Florida','56St',19),  
(22,'Mandy','John',31,'F','Sales','Sales',130000,200,150,'Mandy@company.com','Florida','45St',19),  
(23,'Britney','Berry',45,'F','Sales','Sales',120000,200,100,'Britney@company.com','Florida','86St',19),  
(24,'Adam','Morris',30,'M','Sales','Sales',130000,200,100,'Adam@company.com','Alabama','24St',19),  
(25,'Jack','Mick',29,'M','Sales','Sales',130000,200,100,'Jack@company.com','Hawaii','54St',19),  
(26,'Ben','Ten',43,'M','Sales','Sales',130000,150,100,'Ben@company.com','Hawaii','23St',19),  
(27,'Tom','Fridy',32,'M','Sales','Sales',120000,200,150,'Tom@company.com','Hawaii','23St',1),  
(28,'Morgan','Matt',25,'M','Sales','Sales',120000,200,150,'Morgan@company.com','Hawaii','28St',1),  
(29,'Antoney','Adam',34,'M','Sales','Sales',130000,180,150,'Antoney@company.com','Hawaii','45St',1),  
(30,'Mark','Jon',28,'M','Sales','Sales',120000,200,150,'Mark@company.com','Alabama','43St',1),  
(2,'Joe','Jack',32,'M','Sales','Sales',100000,200,150,'Joe@company.com','California','22St',1),  
(3,'Henry','Ted',31,'M','Senior Sales','Sales',200000,200,150,'Henry@company.com','California','42St',1),  
(4,'Sam','Mark',25,'M','Sales','Sales',100000,120,150,'Sam@company.com','California','23St',1),  
(5,'Max','George',26,'M','Sales','Sales',130000,200,150,'Max@company.com','California','24St',1),  
(8,'John','Ford',26,'M','Senior Sales','Sales',150000,140,100,'Molly@company.com','Alabama','45St',13),  
(9,'Monika','William',33,'F','Sales','Sales',100000,200,100,'Molly@company.com','Alabama','',13),  
(17,'Mick','Berry',44,'M','Senior Sales','Sales',220000,200,150,'Mick@company.com','Florida','',11),  
(12,'Shandler','Bing',23,'M','Auditor','Audit',110000,200,150,'Shandler@company.com','Arizona','',11),  
(14,'Jason','Tom',23,'M','Auditor','Audit',100000,200,150,'Jason@company.com','Arizona','',11),  
(16,'Celine','Anston',27,'F','Auditor','Audit',100000,200,150,'Celine@company.com','Colorado','',11),  
(15,'Michale','Jackson',44,'F','Auditor','Audit',70000,150,150,'Michale@company.com','Colorado','',11),  
(6,'Molly','Sam',28,'F','Sales','Sales',140000,100,150,'Molly@company.com','Arizona','24St',13),  
(7,'Nicky','Bat',33,'F','Sales','Sales',140000,400,100,'Molly@company.com','Arizona','35St',13)  
  
SELECT * FROM employee  
  
DROP TABLE employee  

My code is as follows:

SELECT  
  Sub_cities.*  
FROM (SELECT  
    CASE  
      WHEN employee.first_name = SubQuery.first_name AND  
        SubQuery.city = 'Arizona' THEN employee.first_name  
    END AS arizona  
   ,CASE  
      WHEN employee.first_name = SubQuery.first_name AND  
        SubQuery.city = 'California' THEN employee.first_name  
    END AS california  
   ,CASE  
      WHEN employee.first_name = SubQuery.first_name AND  
        SubQuery.city = 'Hawaii' THEN employee.first_name  
    END AS hawaii  
  FROM (SELECT  
           employee.id  
          ,employee.first_name  
          ,employee.last_name  
          ,employee.age  
          ,employee.sex  
          ,employee.employee_title  
          ,employee.department  
          ,employee.salary  
          ,employee.target  
          ,employee.bonus  
          ,employee.email  
          ,employee.city  
          ,employee.address  
          ,employee.manager_id  
         FROM dbo.employee  
         WHERE employee.city = 'Arizona'  
         OR employee.city = 'Hawaii'  
         OR employee.city = 'California') SubQuery  
      ,dbo.employee) Sub_cities  
GROUP BY Sub_cities.arizona  
        ,Sub_cities.california  
        ,Sub_cities.hawaii  

Thanks

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

3 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-08T02:12:49.13+00:00

    Hi @Carlton Patterson ,

    Please refer below query and check whether it is helpful to you.

     select [Arizona], [California], [Hawaii]  
     from  (  
     select ROW_NUMBER() OVER(PARTITION BY city ORDER BY id) rn   
     ,first_name,city  
      from employee  
     where city in ('Arizona', 'California', 'Hawaii')) s  
     pivot   
     (  
     max(first_name)   
     for city in ([Arizona], [California], [Hawaii]))p  
    

    Output:

    Arizona	California	Hawaii  
    Molly	Allen	Jack  
    Nicky	Joe	Ben  
    Shandler	Henry	Tom  
    Katty	Sam	Morgan  
    Jason	Max	Antoney  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

  2. Carlton Patterson 741 Reputation points
    2021-02-10T08:57:10.853+00:00

    All, please note that I haven't been responding to your helpful suggestions as email notifications have been going to email address carlton@olvin.com.


  3. Carlton Patterson 741 Reputation points
    2021-02-10T08:59:28.027+00:00

    Can someone help explain why email notifications are being sent to carlton@olvin.com ?

    0 comments No comments