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:
- First, I assigned a rowID to each row of the input
;with tableWithRowID as
(
--add an extra column for identification of a row
select c1, c2, c3, ROW_NUMBER() over (order by @@identity) as rowID
from @t
)
- Then I merged the three columns into one using unpivot transformation
,combined as
(
--combine column values into a single column.
select rowID, value from tableWithRowID
unpivot (value for cols in (c1,c2,c3) ) as unpvt
)
- Then I sorted that merged result grouped by rowID (horizontal sorting). In this step, I added a new column colID such that the smallest value gets colID=1, the next one gets colID=2 and so on.
,sorted as
(
--sort values in each row (grouped by rowID). This sorts the original data horizontally
select rowID, value, ROW_NUMBER() over (partition by rowID order by value) as colID
from combined
)
- Then I used pivot to split the values into three columns based on that sorting
,splitted as
(
--split into columns again (based on that sorting)
select rowID, [1] as c1, [2] as c2, [3] as c3
from sorted
pivot ( min(value) for colID in ([1],[2],[3])) as pvt
)
- Finally I sorted the result (vertical sorting) and displayed distinct rows
--sort vertically and select distinct select distinct c1,c2,c3 from splitted order by c1, c2, c3
That’s all, the nice feature about the solution is that its highly expandable; to configure it for greater no. of columns, we just need to modify the pivot/unpivot clauses. Here’s the complete source code:
--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
--solution
;with tableWithRowID as
(
--add an extra column for identification of a row
select c1, c2, c3, ROW_NUMBER() over (order by @@identity) as rowID
from @t
)
,combined as
(
--combine column values into a single column.
select rowID, value from tableWithRowID
unpivot (value for cols in (c1,c2,c3) ) as unpvt
)
,sorted as
(
--sort values in each row (grouped by rowID). This sorts the original data horizontally
select rowID, value, ROW_NUMBER() over (partition by rowID order by value) as colID
from combined
)
,splitted as
(
--split into columns again (based on that sorting)
select rowID, [1] as c1, [2] as c2, [3] as c3
from sorted
pivot ( min(value) for colID in ([1],[2],[3])) as pvt
)
--sort vertically and select distinct
select distinct c1,c2,c3
from splitted
order by c1, c2, c3
