Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | A Conditional Aggregation – The ToCity Puzzle | SQL Interview Question

In this puzzle you have to find the create a new column called ToCity. The Value of this column will be the city for which the flag value is 2 for each Id. Now if you do not have any record with flag = 2 for any id then repeat the same city in the ToCity column.

Please check the sample input and the expected output.

Sample Input

Id Flag FromCity
1 1 Gurgaon
1 2 Pune
2 1 Gurgaon
2 2 Chicago
3 1 NY
3 2 Las Vegas
4 1 Gurgaon
5 1 Gurgaon
6 1 Gurgaon
7 1 Gurgaon

Expected Output

id flag FromCity ToCity
1 1 Gurgaon Pune
2 1 Gurgaon Chicago
3 1 NY Las Vegas
4 1 Gurgaon Gurgaon
5 1 Gurgaon Gurgaon
6 1 Gurgaon Gurgaon
7 1 Gurgaon Gurgaon

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

--

CREATE TABLE MoveRowTOColumn
(
	 ID INT
	,Flag INT
	,City VARCHAR(100)
)
GO

INSERT INTO MoveRowTOColumn VALUES
(1,1,'Gurgaon'),
(1,2,'Pune'),
(2,1,'Gurgaon'),
(2,2,'Chicago'),
(3,1,'NY'),
(3,2,'Las Vegas'),
(4,1,'Gurgaon'),
(5,1,'Gurgaon'),
(6,1,'Gurgaon'),
(7,1,'Gurgaon')
GO

SELECT * FROM MoveRowTOColumn
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

--

SELECT * FROM
(
	SELECT Id, Flag , City FromCity 
		, ISNULL( MAX(CASE WHEN Flag = 2 THEN City ELSE NULL END) OVER(PARTITION BY ID) , City ) ToCity
	FROM MoveRowTOColumn
)u WHERE Flag = 1

--

Output-1

--                            


Id          Flag        FromCity        ToCity         
----------- ----------- --------------- -------------- 
1           1           Gurgaon         Pune           
2           1           Gurgaon         Chicago        
3           1           NY              Las Vegas      
4           1           Gurgaon         Gurgaon        
5           1           Gurgaon         Gurgaon        
6           1           Gurgaon         Gurgaon        
7           1           Gurgaon         Gurgaon        
                                                       
(7 rows affected)                                      
                                                       
                                   

--

SOLUTION – 2

--

SELECT distinct m1.id,m1.flag,m1.city FromCity ,COALESCE(m2.city,m1.city)  ToCity
from MoveRowTOColumn m1 LEFT join MoveRowTOColumn m2 ON m1.id = m2.ID and m2.flag>m1.flag
WHERE m1.flag !=2

--

Output-2

--                            


Id          Flag        FromCity        ToCity         
----------- ----------- --------------- -------------- 
1           1           Gurgaon         Pune           
2           1           Gurgaon         Chicago        
3           1           NY              Las Vegas      
4           1           Gurgaon         Gurgaon        
5           1           Gurgaon         Gurgaon        
6           1           Gurgaon         Gurgaon        
7           1           Gurgaon         Gurgaon        
                                                       
(7 rows affected)                                      
                                                       


--

SOLUTION – 3 | THE APPLY Operator

--

SELECT x.Id,x.Flag,x.City FromCity , ISNULL(a.City,x.City) ToCity 		
FROM MoveRowTOColumn x
OUTER APPLY 
(
	SELECT TOP 1 City FROM 
	MoveRowTOColumn y WHERE 
	x.Id = y.Id and y.Flag = 2
)a
WHERE a.City <> x.City OR a.City IS NULL

--

Output-3

--                            


Id          Flag        FromCity              ToCity          
----------- ----------- -------------- ------ --------------- 
1           1           Gurgaon               Pune            
2           1           Gurgaon               Chicago         
3           1           NY                    Las Vegas       
4           1           Gurgaon               Gurgaon         
5           1           Gurgaon               Gurgaon         
6           1           Gurgaon               Gurgaon         
7           1           Gurgaon               Gurgaon         
                                                              
(7 rows affected)                                             
                                                              

--

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

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com