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 :
- 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”.
- Sort the rows vertically: This is the regular sorting that we are familiar with.
- 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:
- First, I assigned a rowID to each row of the input
- Then I merged the three columns into one using unpivot transformation
- Then I sorted that merged result grouped by rowID (horizontal sorting)
- Then I used pivot to split the values into three columns based on that sorting
- 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 »
