This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to sort data vertically and horizontally using PIVOT and UNPIVOT
Input Data:
|
1 2 3 4 5 6 7 8 |
col1 col2 col3 ---- ---- ---- 2 1 3 3 2 1 Z X Y B C D Y Z X B C A |
Expected Output:
|
1 2 3 4 5 6 |
col1 col2 col3 ---- ---- ---- 1 2 3 A B C B C D X Y Z |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE PivotSort ( col1 CHAR(1) ,col2 CHAR(1) ,col3 CHAR(1) ) INSERT INTO PivotSort (col1, col2, col3) VALUES ('2','1','3'),('3','2','1'),('Z','X','Y') ,('B','C','D'),('Y','Z','X'),('B','C','A') GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
;WITH unpivotted AS ( SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rownum, * FROM PivotSort ) t UNPIVOT (vals FOR col IN (col1,col2,col3)) p ) , ordered_cols AS ( SELECT rownum, vals, ROW_NUMBER() OVER (PARTITION BY rownum ORDER BY vals) AS colnum FROM unpivotted ) SELECT DISTINCT [1] [col1], [2] [col2], [3] [col3] FROM ordered_cols PIVOT (MAX(vals) FOR colnum IN ([1],[2],[3])) p ORDER BY [1], [2], [3] |
Please try the different solution for this puzzle and share it via comment...
Leave a Reply