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 🙂

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