SQL Puzzle | Remove NULLs via row-wise pattern

This question is good. Here we have to remove Nulls from each row. So effectively we have 4 rows. In the first row we should get 10, in the second row we should get 20 after ignoring all null values and so on….

For details please check out the sample input and the expected output below-

Sample Input

c1 c2 c3 c4 c5
NULL 10 NULL NULL NULL
NULL NULL 20 NULL NULL
NULL NULL NULL 30 NULL
40 NULL NULL NULL NULL

Expected Output

1 2 3 4
10 20 30 40

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.

Script | use below script to create table & insert some sample data

 ``` -- CREATE TABLE TestNulls ( c1 TinyINT ,c2 TinyINT ,c3 TinyINT ,c4 TinyINT ,c5 TinyINT ) GO INSERT INTO TestNulls VALUES (NULL ,10 ,NULL, NULL, NULL), (NULL ,NULL, 20, NULL, NULL), (NULL ,NULL, NULL, 30, NULL), (40 ,NULL, NULL, NULL, NULL) GO -- ```

Solution 1

 ``` -- SELECT , , ,  FROM (SELECT isnull(c1,0)+isnull(c2,0)+isnull(c3,0)+isnull(c4,0)+isnull(c5,0) as Sum1,ROW_NUMBER () over (order by (select 0)) as c FROM testnulls ) AS SourceTable PIVOT ( sum(Sum1) FOR c IN ( , , , ) ) AS PivotTable; -- ```

Solution 2

 ``` -- SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER by NEWID() ) b,* FROM (Select COALESCE(c1,c2,c3,c4,c5) AS COL from TestNulls) a WHERE COL is not NULL ) as s PIVOT ( MAX(COL) FOR b IN (,,,) ) AS pvt -- ```

Solution 3

 ``` -- Select Sum(o1) AS ,Sum(o2) AS ,Sum(o3) AS ,sum(o4) AS  from ( Select (select T.C2 from TestNulls T where T.c2 = DT.C) as o1 , (select T.c3 from TestNulls T where T.c3 = DT.C) as o2 , (select T.c4 from TestNulls T where T.c4 = DT.C) as o3 , (select T.c1 from TestNulls T where T.c1 = DT.C) as o4 from ( select IsNull(c1,0) + IsNull(C2,0) +IsNull(c3,0)+IsNull(c4,0)+IsNull(c5,0) as C from TestNulls ) DT ) DT2 -- ```

Well the first method is the best method. Since in that we are not doing explicit sort. 🙂

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com