SQL PUZZLE | The Multi Column Creation Puzzle (Multi-JOIN/Conditional Aggregation Puzzle) | SQL Interview Question

In this puzzle you have to create three new columns based on TableB Ids 1, 2 and 3. Relationship between the tables is like TableA(Id) -> TableB(TableAId). Please see the sample input and expected output.

Sample Input

TableA

Id Val
1 P
2 Q

TableB

Id TableAId Amt
1 1 10
1 2 20
2 2 40
3 1 60

Expected output

Id Val FirstAmount SecondAmount ThirdAmount
1 P 10 NULL 60
2 Q 20 40 NULL

Script – DDL and INSERT sample data

 ```-- CREATE TABLE myData1 ( Id INT ,Vals TEXT ) Go INSERT INTO myData1 VALUES (1,'Hello'), (2,''), (3,''), (4,'Where r u ?') GO SELECT * FROM myData1 GO -- ```

SOLUTION – 1

 ```-- SELECT a.Id , a.Val , b1.Amt [FirstAmount] , b2.Amt [SecondAmount], b3.Amt [ThirdAmount] FROM TableA a LEFT JOIN TableB b1 ON a.id = b1.TableAId AND b1.Id = 1 LEFT JOIN TableB b2 ON a.id = b2.TableAId AND b2.Id = 2 LEFT JOIN TableB b3 ON a.id = b3.TableAId AND b3.Id = 3 WHERE b1.Amt IS NOT NULL OR b2.Amt IS NOT NULL OR b3.Amt IS NOT NULL -- ```

Output – 1

 ```-- Id Val FirstAmount SecondAmount ThirdAmount ----------- ---------- ----------- ------------ ----------- 1 P 10 NULL 60 2 Q 20 40 NULL (2 rows affected) -- ```

SOLUTION – 2

 ```-- SELECT * FROM ( SELECT a.Id , a.Val , MAX(CASE WHEN b1.Id = 1 THEN b1.Amt END) [FirstAmount] , MAX(CASE WHEN b1.Id = 2 THEN b1.Amt END) [SecondAmount] , MAX(CASE WHEN b1.Id = 3 THEN b1.Amt END) [ThirdAmount] FROM TableA a LEFT JOIN TableB b1 ON b1.TableAId = a.Id GROUP BY a.Id , a.Val )x WHERE x.FirstAmount IS NOT NULL OR x.SecondAmount IS NOT NULL or x.ThirdAmount IS NOT NULL -- ```

Output – 2

 ```-- Id Val FirstAmount SecondAmount ThirdAmount ----------- ---------- ----------- ------------ ----------- 1 P 10 NULL 60 2 Q 20 40 NULL (2 rows affected) -- ```

Enjoy 🙂

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/