SQL Puzzle | Remove NULLs via row-wise pattern (Solution Time :) – Multiple Solution & the best one)

Tags

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.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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 
 [1], [2], [3], [4]
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 ( [1], [2], [3], [4])
) 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 ([1],[2],[3],[4])
)
AS pvt

--

Solution 3


--

Select Sum(o1) AS [1],Sum(o2) AS [2],Sum(o3) AS [3],sum(o4) AS [4] 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

10 thoughts on “SQL Puzzle | Remove NULLs via row-wise pattern (Solution Time :) – Multiple Solution & the best one)”

Abhishek Dwivedi (@A_dwivedi) said:

July 27, 2016 at 5:02 PM

select (select c2 from TestNulls where c2 is not null )as a,(select c3 from TestNulls where c3 is not null)as b ,
(select c4 from TestNulls where c4 is not null ) as c,(select c1 from TestNulls where c1 is not null) as d

LikeLike

Anand kumar KG (@AnandkumarNlk) said:

July 27, 2016 at 8:00 PM

select
sum (coalesce(c2,0)) as [1] ,
sum (coalesce(c3,0)) as [2] ,
sum (coalesce(c4,0)) as [3] ,
sum (coalesce(c1,0)) as [4]
from TestNulls

LikeLike

Pingback: SQL Puzzle | The Tricky NULL Puzzle – 1 | Improving my SQL BI Skills
Pingback: SQL Puzzle | The Tricky NULL Puzzle – 2(CONCAT) | Improving my SQL BI Skills
Pingback: SQL Puzzle | THE AMAZING NULL PROBLEM | SQL Interview Question | Improving my SQL BI Skills
Pingback: SQL Puzzle | DISTINCT Count with NULL Puzzle – A SINGLE SELECT? | SQL Interview Question | Improving my SQL BI Skills
Sappho03 (@sappho0101) said:

April 10, 2018 at 6:24 PM

select max(c2) [1],max(c3) [2],max(c4) [3], max(c1) [4] from TestNulls 🙂

LikeLike

sanjay singh said:

May 3, 2018 at 3:02 PM

;with cte
as
(
select coalesce (c1,c2,c3,c4,c5)as c1 from #TestNulls
)
select max(case when c1=10 then c1 end ) As c1 ,
max(case when c1=20 then c1 end ) As c2 ,
max(case when c1=30 then c1 end ) As c3 ,
max(case when c1=40 then c1 end ) As c4
from cte a

LikeLike

- Pawan Kumar Khowal said:
  
  May 5, 2018 at 9:50 AM
  
  Excellent answer Sanjay !
  
  LikeLike
  
revank219 said:

June 9, 2021 at 8:19 PM

;with cte as (
select coalesce(c1,c2,c3,c4)as val,row_number()over(order by (select 1))rnk from TestNulls
)
select [1],[2],[3],[4] from cte
PIVOT(
max(val) for rnk in ([1],[2],[3],[4])
)A

LikeLike