Tags

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


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 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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