TSQL Challenge 10: Horizontal and Vertical sorting of a result set

TSQL Challenge 10 was an interesting one. We needed to sort a result set horizontally as well as vertically. That is, given the following as an input:

We need to write a query that :

  1. Sort the values horizontally: Arrange the values from smallest to the largest. for example, the first row contains values “2”, “1” and “3”, it should be arranged as “1”, “2” and “3”.
  2. Sort the rows vertically: This is the regular sorting that we are familiar with.
  3. Remove duplicates: Duplicate rows should be removed from the final output.

And here’s the expected output:

I tackled the problem systematically in series of steps, each represented by a CTE:

  1. First, I assigned a rowID to each row of the input
  2. Then I merged the three columns into one using unpivot transformation
  3. Then I sorted that merged result grouped by rowID (horizontal sorting)
  4. Then I used pivot to split the values into three columns based on that sorting
  5. Finally I sorted the result (vertical sorting) and displayed distinct rows

Here are the CTE definitions along with the result at each step: Read the rest of this entry »