This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to remove all NULL records and arrange NON NULL value in an Order.
Input Data:
1 2 3 4 5 6 |
Col1 Col2 Col3 Col4 ------ ------ ------ ------ NULL 10 NULL NULL 20 NULL NULL NULL NULL NULL NULL 30 NULL NULL 40 NULL |
Expected Output:
1 2 3 |
Col1 Col2 Col3 Col4 ----------- ----------- ----------- ----------- 10 20 30 40 |
Create a table with data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE tbl_Numbers ( Col1 SmallINT ,Col2 SmallINT ,Col3 SmallINT ,Col4 SmallINT ) GO INSERT INTO tbl_Numbers VALUES (NULL,10,NULL,NULL) ,(20,NULL,NULL,NULL) ,(NULL,NULL,NULL,30) ,(NULL,NULL,40,NULL) GO |
Solution:
1 2 3 4 5 6 7 8 9 10 |
SELECT [1] as Col1, [2] as Col2, [3] as Col3, [4] as Col4 FROM (SELECT isnull(Col1,0)+isnull(Col2,0)+isnull(Col3,0)+isnull(Col4,0) as SumAll,ROW_NUMBER () over (order by (select 0)) as t FROM tbl_Numbers ) AS SourceTable PIVOT ( sum(SumAll) FOR t IN ( [1], [2], [3], [4]) ) AS PivotTable |
Please try the different solution for this puzzle and share it via comment...