Tags

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


SQL Puzzle | The Compare * 100 Puzzle | SQL Interview Question

In this puzzle you have to generate a new column called FinalOutput. The logic for the new column is that you have to check if value of Length1 = Length1 * 100 for each value for col1 and col2, If yes then you need to concatenate the small value with the larger value separating by |. If not then show the value directly. Can you do that using a single select?. Please check out the sample input and the expected output.

Sample Input

col1 col2 Length1
A APD 23
A APD 2300
A APD 45
A APD 78
B BPD 45
B BPD 4500
B BPD 450
NULL CPD 98
NULL CPD 9800
NULL CPD 983

Expected Output

col1 col2 Length1 FinalOutput
A APD 23 23 | 2300
A APD 2300 23 | 2300
A APD 45 45
A APD 78 78
B BPD 45 45 | 4500
B BPD 4500 45 | 4500
B BPD 450 450
NULL CPD 98 98 | 9800
NULL CPD 9800 98 | 9800
NULL CPD 983 983

Script – DDL and INSERT Sample Data

--

CREATE Table Combine 
(
    col1 varchar(MAX),
    col2 varchar(MAX),
    Length1 float
)
GO

INSERT INTO Combine VALUES
('A','APD',23),
('A','APD',2300),
('A','APD',45),
('A','APD',78),
('B','BPD',45),
('B','BPD',4500),
('B','BPD',450),
('NULL','CPD',98),
('NULL','CPD',9800),
('NULL','CPD',983)
GO

SELECT * FROM Combine
GO

--

–SOLUTION 1 : THE SUM … OVER() WITH IIF

--

SELECT a.*, 
		IIF
		(
			b.Length1 IS NOT NULL,
			CONCAT(IIF(a.Length1>b.Length1,b.Length1,a.Length1),' | ',IIF(a.Length1>b.Length1,a.Length1,b.Length1)),
			CONCAT(a.Length1,'')
		)FinalOutput 
FROM Combine
a LEFT JOIN Combine b ON a.col1 = b.col1 AND a.col2 = b.col2
AND ( a.Length1 * 100 = b.Length1 OR a.Length1 = b.Length1 * 100 )

--

OUTPUT – 1

--

col1       col2         Length1                FinalOutput 
---------- ------------ ---------------------- ----------- 
A          APD          23                     23 | 2300   
A          APD          2300                   23 | 2300   
A          APD          45                     45          
A          APD          78                     78          
B          BPD          45                     45 | 4500   
B          BPD          4500                   45 | 4500   
B          BPD          450                    450         
NULL       CPD          98                     98 | 9800   
NULL       CPD          9800                   98 | 9800   
NULL       CPD          983                    983         
                                                           
(10 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 Performance 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