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

Expected Output

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<c2,c1,c2) c1
            ,IIF(c1<c2,c2,c1) c2
FROM DistinctRecordsMultipleColumns a
GROUP BY IIF(c1<c2,c1,c2),IIF(c1<c2,c2,c1)


--

OUTPUT – 2 | Using IIFs

--

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

(4 rows affected)

--

Reference – https://msbiskills.com/2018/02/15/sql-puzzle-distinct-values-from-multiple-columns-puzzle-sql-interview-question/

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.

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