How to remove nulls from result

Question

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

Answer

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.

Answer

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.

Answer

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

Share via

How to remove nulls from result

3 answers

Your answer