Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

http://MSBISkills.com

Advertisements