This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to find a distinct value for column 3 and accordingly move additional data of Column 1 to Column 2.
Input Data:
|
1 2 3 4 5 6 7 8 |
Col1 Col2 Col3 ----------- ----------- ----------- 888 NULL 111 555 NULL 333 777 NULL 444 222 NULL 333 666 NULL 444 999 NULL 111 |
Expected Output:
|
1 2 3 4 5 |
Col1 Col2 Col3 ----------- ----------- ----------- 222 888 111 666 555 333 999 777 444 |
Create a table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE tbl_DataColumn ( Col1 INT ,Col2 INT ,Col3 INT ) GO INSERT INTO tbl_DataColumn VALUES (888,NULL ,111) ,(555,NULL ,333) ,(777,NULL ,444) ,(222,NULL ,333) ,(666,NULL ,444) ,(999,NULL ,111) GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
;WITH CTE AS ( SELECT Col1 , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM tbl_DataColumn ), CTE1 AS ( SELECT Col3 ,COUNT(*) OVER() as Counts ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) t1 FROM tbl_DataColumn GROUP BY Col3 ) ,CTE2 AS ( SELECT Col1 ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) t2 FROM CTE WHERE rnk > (SELECT TOP 1 Counts FROM CTE1 ) ) ,CTE3 AS ( SELECT Col1 Col2 ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) t3 FROM CTE WHERE rnk <= (SELECT TOP 1 Counts FROM CTE1 ) ) SELECT CTE2.Col1 ,Col2 ,CTE1.Col3 FROM CTE1 INNER JOIN CTE2 ON CTE1.t1 = CTE2.t2 INNER JOIN CTE3 ON CTE2.t2 = CTE3.t3 |
Please try the different solution for this puzzle and share it via comment...
