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”.

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 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/