TSQL Challenge #10 – Horizontal and Vertical Sorting
Wow! Free training to solve TSQL Challenges
Along with TSQL Challenge #10, we are introducing a FREE training program to help more people capable of solving real-life TSQL challenges. If you are able to solve a given challenge, please go ahead and send us your entries. However, if you could not solve it, register for a FREE training webcast (after the challenge closes) that will help you to learn how to solve problems similar to the one given in the challenge. Details of the training program is given at the end of this post.
Challenge #10
I think this challenge will be very interesting as you need to sort the data horizontally and vertically to solve this. To understand this challenge better, let us look at the source data and expected result.
Source Data
C1 C2 C3
---- ---- ----
2 1 3
3 2 1
Z X Y
B C D
Y Z X
B C A
Expected Result
Here is the expected output. Write a single query that operates on the above data and produces the output given below.
C1 C2 C3
---- ---- ----
1 2 3
A B C
B C D
X Y Z
This challenge requires the following steps:
- Sort the values horizontally. Arrange the values from smallest to the largest. for example, the first row contains values “2”, “1” and “3”. This should be arranged as “1”, “2” and “3”.
- Sort the rows vertically. This is the regular sorting that we are familiar with. Final result should be sorted as shown in the ‘expected result’ listing.
- Remove duplicates – Duplicate rows should be removed from the final output.
Sample Data
You can use the following script to generate the 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
Notes
- As usual, write a single query that produces the expected output. CTEs are acceptable.
- Send your entries to tc@beyondrelational.com
- Do not include your solutions in the body of the email. Send them as an attachment in the email.
- Add ‘TSQL Challenge #10’ in the subject line of the email.
- Last date to submit your entries: 29 June 2009
- Use this forum for any questions related to TSQL Challenge #10
Free Training Webcast
With this challenge, we start arranging a free training webcast for those people who are interested in the challenge, but not able to solve it. The training event will be right after the closing date of the challenge. People attending the training webcast can still send their solutions, but will be evaluated separately. Winners will be identified only from the first submission. If some one writes a good solution after attending the training webcast, we will publish the solution, but will not be considered as a winner.
The training webcast may not directly try to solve the given challenge. However, it will explain how to write SET based queries that move data from one shape to another and the tips and tricks explained in the webcast can be used to solve the given challenge.
To attend the training webcast, register at: http://tsqlchallenge10.eventbrite.com/