Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL Server Data Tools, SQL SERVER Interview questions, SQL SERVER Puzzles, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, SSRS Interview Questions, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ The Sorting (Horizontal & Vertical) Puzzle ]
Original puzzle link – http://beyondrelational.com/blogs/tc/archive/2009/06/23/tsql-challenge-10-horizontal-and-vertical-sorting.aspx
The puzzle is simple. In this puzzle we needed to sort a result set horizontally as well as vertically. Please check out the sample input and expected output for details.
Sample Input
| c1 | c2 | c3 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| Z | X | Y |
| B | C | D |
| Y | Z | X |
| B | C | A |
Expected output
| C1 | C2 | C3 |
| 1 | 2 | 3 |
| A | B | C |
| B | C | D |
| X | Y | Z |
Rules/Restrictions
- The solution should be should use “SELECT” statement or “CTE”.
- Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
Script
Use the below script to generate the source table and fill them up with the sample data.
--
CREATE TABLE Sorting
(
c1 CHAR(1),
c2 CHAR(1),
c3 CHAR(1)
)
insert into Sorting (c1, c2, c3) values ('2','1','3')
insert into Sorting (c1, c2, c3) values ('3','2','1')
insert into Sorting (c1, c2, c3) values ('Z','X','Y')
insert into Sorting (c1, c2, c3) values ('B','C','D')
insert into Sorting (c1, c2, c3) values ('Y','Z','X')
insert into Sorting (c1, c2, c3) values ('B','C','A')
--
|
UPDATE – 20-Apr-2015 – Solution 1 & 2
--
--Solution 1 | Using XML and CrossApply
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (ORDER BY c1) AS id,
c1,c2,c3,
CAST('<x>'+c1+'</x><x>'+c2+'</x><x>'+c3+'</x>'
AS XML) AS xmlcol
FROM Sorting
)
,CTE1 AS
(
SELECT id,splitdata,rnk FROM CTE s
CROSS apply
(
SELECT ProjectData.D.value('.', 'varchar(50)') as splitdata
, ROW_NUMBER() over (partition by id
order by (ProjectData.D.value('.', 'varchar(50)'))) rnk
FROM s.xmlcol.nodes('x') as ProjectData(D)
) b
)
SELECT DISTINCT [1] [C1], [2] [C2], [3] [C3]
FROM CTE1
PIVOT
(MAX(splitdata) FOR rnk IN ([1],[2],[3])) p
ORDER BY [1], [2], [3]
--Solution 2 | Using Pivot and Unpivot
;WITH
unpivotted AS (
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rownum, *
FROM Sorting
) t UNPIVOT (vals FOR col IN (c1,c2,c3)) p
)
,
ordered_cols AS (
SELECT rownum, vals,
ROW_NUMBER() OVER (
PARTITION BY rownum ORDER BY vals) AS colnum
FROM unpivotted
)
SELECT DISTINCT [1] [C1], [2] [C2], [3] [C3]
FROM ordered_cols
PIVOT
(MAX(vals) FOR colnum IN ([1],[2],[3])) p
ORDER BY [1], [2], [3]
--
|
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Improving my SQL BI Skills 
Hi I have done it in oracle and please find the query below-
WITH TEMP AS (
SELECT A.C1,A.RN,ROW_NUMBER() OVER(PARTITION BY A.RN ORDER BY A.C1) AS ORD FROM (
SELECT C1,ROWNUM AS RN FROM SORTING
UNION ALL
SELECT C2,ROWNUM AS RN FROM SORTING
UNION ALL
SELECT C3,ROWNUM AS RN FROM SORTING
ORDER BY 2,1) A)
SELECT DISTINCT T1.C1,T2.C1,T3.C1 FROM TEMP T1,TEMP T2,TEMP T3 WHERE T1.ORD+1=T2.ORD AND T2.ORD+1=T3.ORD AND T1.RN=T2.RN AND T2.RN=T3.RN ORDER BY 1;
LikeLike
select distinct c1=
case
when c3<c2 and c3<c1 then c3
when c2<c3 and c2<c1 then c2
else c1
end,
c2=
case when (c3<c1 and c1c1 and c1>c2) then c1
when (c1<c2 and c2c2 and c2>c3) then c2
else c3
end, c3=
case
when c3>c2 and c3>c1 then c3
when c2>c3 and c2>c1 then c2
else c1
end
from sorting
LikeLike