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

Advertisements

July 2, 2009 at 11:51 PM

… Great work …

Since, it is first time I participated in TSQL Challenge, that is why I become little bit confuse.

I thought, I need to make it done using single query means no temp table, Pivot and etc.

That is why I make it a bit complex by using subquries …

however, I have manage to get the output … will share that with you

July 22, 2009 at 5:25 PM

Nice to learn that, Usman. I look forward to seeing your solution.

Also, if you want it strictly in a big single select query, you can replace CTEs by “derived tables”. Here’s the same solution:

September 18, 2009 at 10:13 AM

Hey Syed!

Same way i’ve tried for this req. its clear dude!