T-SQL Challenge

I think this challenge will be very interesting as you need to sort the data horizontally and vertically to solve this. To understand this challenge better, let us look at the source data and expected result.

Source Data

C1   C2   C3

---- ---- ----

2    1    3

3    2    1

Z    X    Y

B    C    D

Y    Z    X

B    C    A

Expected Result

Here is the expected output. Write a single query that operates on the above data and produces the output given below.

C1   C2   C3

---- ---- ----

1    2    3

A    B    C

B    C    D

X    Y    Z

This challenge requires the following steps:

1. Sort the values horizontally. Arrange the values from smallest to the largest. for example, the first row contains values “2”, “1” and “3”. This should be arranged as “1”, “2” and “3”.
2. Sort the rows vertically. This is the regular sorting that we are familiar with. Final result should be sorted as shown in the ‘expected result’ listing.
3. Remove duplicates – Duplicate rows should be removed from the final output.

Sample Data

You can use the following script to generate the sample data.

DECLARE @t TABLE (

 c1 CHAR(1),

 c2 CHAR(1),

 c3 CHAR(1)

)



insert into @t (c1, c2, c3) values ('2','1','3')

insert into @t (c1, c2, c3) values ('3','2','1')

insert into @t (c1, c2, c3) values ('Z','X','Y')

insert into @t (c1, c2, c3) values ('B','C','D')

insert into @t (c1, c2, c3) values ('Y','Z','X')

insert into @t (c1, c2, c3) values ('B','C','A')



SELECT * FROM @t

Notes

1. As usual, write a single query that produces the expected output. CTEs are acceptable.

Solution :

DECLARE @t TABLE (

 c1 CHAR(1),

 c2 CHAR(1),

 c3 CHAR(1)

)



insert into @t (c1, c2, c3) values ('2','1','3')

insert into @t (c1, c2, c3) values ('3','2','1')

insert into @t (c1, c2, c3) values ('Z','X','Y')

insert into @t (c1, c2, c3) values ('B','C','D')

insert into @t (c1, c2, c3) values ('Y','Z','X')

insert into @t (c1, c2, c3) values ('B','C','A')

 
SELECT DISTINCT CASE WHEN c1<c2 AND c1<c3 THEN c1 
    WHEN c2<c1 AND c2<c3 THEN c2 ELSE c3 END C1, 
CASE WHEN C1>C2 AND C1<C3 THEN C1 WHEN C1<C2 AND C1>C3 THEN C1  
     WHEN C2>C1 AND C2<C3 THEN C2 WHEN C2<C1 AND C2>C3 THEN C2  
     ELSE C3 END C2, 
CASE WHEN c1>c2 AND c1>c3 THEN c1 WHEN c2>c1 AND c2>c3 THEN c2  
     ELSE c3 END C3
FROM @t