SQL Puzzle | Number to call Puzzle

In this puzzle you have to Generate a new column called Nums(Number to call).

The conditions to generate the new column (Nums) are-

1. If the EmpMobileNumber is not null then take the value from the EmpMobileNumber and append ‘EmpMobileNumber’ to the data.
2. If the EmpResidenceNumber is not null then take the value from the EmpResidenceNumber and append ‘EmpResidenceNumber’ to the data.
3. If both EmpMobileNumber and EmpResidenceNumber are NOT NULL then take the value from the EmpMobileNumber and append ‘EmpMobileNumber’ to the data.
4. If both EmpMobileNumber and EmpResidenceNumber are NULL then we should get NULL as output.
5. The challenge is to do this without USING CASE Statement.

For more details please check the sample input and expected output.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE EmployeeNumber
(
	 EmpId INT 
	,EmpName VARCHAR(10)
	,EmpMobileNumber VARCHAR(100)
	,EmpResidenceNumber VARCHAR(100)
)
GO

INSERT INTO EmployeeNumber VALUES
(1,'Avtaar','9873686533',NULL),
(2,'Kishan',NULL,'9873686513'),
(3,'Chander','9873682233','9873680233'),
(4,'Sharlee','9873111133','7873682233'),
(5,'Ishu',NULL,'9873686598'),
(6,'Ram','9873761211',NULL),
(7,'Vaibhav',NULL,NULL),
(8,'Mayank',NULL,'8761231231')
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1 | WITHOUT CASE Statement

--

SELECT EmpId , EmpName
	, ISNULL ( EmpMobileNumber + ' MobileNumber' , EmpResidenceNumber + ' ResidenceNumber' ) Nums
FROM EmployeeNumber


--

Output-1 | WITHOUT CASE Statement

--                            

EmpId       EmpName    Nums                                
----------- ---------- ----------------------------------- 
1           Avtaar     9873686533 MobileNumber             
2           Kishan     9873686513 ResidenceNumber          
3           Chander    9873682233 MobileNumber             
4           Sharlee    9873111133 MobileNumber             
5           Ishu       9873686598 ResidenceNumber          
6           Ram        9873761211 MobileNumber             
7           Vaibhav    NULL                                
8           Mayank     8761231231 ResidenceNumber          
                                                           
(8 rows affected)                                          
                                                           
                                  
                                
--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MsbiSkills.com