SQL Puzzle | The Distinct Records Multi-Col Puzzle – 1

In this puzzle you have to find out distinct values from multiple columns. In the input table you have 2 columns names c1 and c2. E.g.

c1 -> a
c2 -> b

c1 -> b
c2 -> a

The above will be considered as a single value , for these kind of cases we need the first value. In this case it is a,b for c1 and c2 respectively.

Please check out the sample input values and sample expected output below.

Sample Input

c1 c2
a b
b a
c d
e f
g g
g g

Expected Output

c1 c2
a b
c d
e f
g g

Script – DDL and INSERT Sample Data

 -- CREATE TABLE DistinctRecordsMultipleColumns ( c1 VARCHAR(10) ,c2 VARCHAR(10) ) GO INSERT INTO DistinctRecordsMultipleColumns VALUES ('a','b'), ('b','a'), ('c','d'), ('e','f'), ('g','g'), ('g','g') GO SELECT * FROM DistinctRecordsMultipleColumns GO --

SOLUTION 1 | Using CROSS APPLY

 -- SELECT x.c c1 ,x.d c2 FROM DistinctRecordsMultipleColumns a CROSS APPLY ( VALUES (c1,c2),(c2,c1) )x(c,d) WHERE x.c<=x.d GROUP BY x.c,x.d --

OUTPUT – 1 | Using CROSS APPLY & Values Clause

 -- c1 c2 ---------- ---------- a b c d e f g g (4 rows affected) --

SOLUTION 2 | Using IIFs

 -- SELECT IIF(c1

OUTPUT – 2 | Using IIFs

 -- c1 c2 ---------- ---------- a b c d e f g g (4 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/