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:

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:

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:

  • 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
)

cte1

  • 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
)

cte2

  • 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
)

cte3

  • 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
)

cte4

  • 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

output

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
About these ads

3 Responses to “TSQL Challenge 10: Horizontal and Vertical sorting of a result set”

  1. Agha Usman Says:

    … 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

  2. Syed Mehroz Alam Says:

    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:

    --sort vertically and select distinct 
    select distinct c1,c2,c3 
    from 
    (
    	--split into columns again (based on that sorting)
    	select rowID, [1] as c1, [2] as c2, [3] as c3
    	from 
    	(
    		--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 
    		(
    			--combine columns into a single row. 
    			select rowID, value 
    			from 
    			(
    				--add an extra column for identification of a row		
    				select c1, c2, c3, ROW_NUMBER() over (order by @@identity) as rowID
    				from @t
    			) as tableWithRowID
    			unpivot (value for cols in (c1,c2,c3) ) as unpvt
    		) as combined
    	) as sorted
    	pivot ( min(value) for colID in ([1],[2],[3])) as pvt
    ) as splitted
    order by c1, c2, c3
    
  3. Shiva Says:

    Hey Syed!

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 55 other followers

%d bloggers like this: