How can I get data in single row when multiple columns data have null in some columns?

Question

How can I get data in single row when multiple columns data have null in some columns? Following is the scenario

col1    col2   col3  col4
----- ------ --------------- 
1      NULL    NULL  NULL
NULL   2       NULL  NULL
NULL   NULL    3     NULL
NULL   NULL    NULL  4

I want output like this

col1    col2   col3  col4
----- ------ ---------------
1      2        3    4

Answer 1

You can use aggregate functions as below:

select min(col1) as col1,min(col2) as col2,min(col3) as col3,min(col4) as col4 from t 
select max(col1) as col1,max(col2) as col2,max(col3) as col3,max(col4) as col4 from t 
select sum(col1) as col1,sum(col2) as col2,sum(col3) as col3,sum(col4) as col4 from t 
select avg(col1) as col1,avg(col2) as col2,avg(col3) as col3,avg(col4) as col4 from t 

However Min or Max or more meaningful than the Avg and Sum in this scenario.

Answer 2

select max(col1) as col1, 
       max(col2) as col2, 
       max(col3) as col3, 
       max(col4) as col4
from your_table

Answer 3

Try this way.

SELECT DISTINCT 
(SELECT TOP 1 Col1 FROM  TestTable WHERE Col1 IS NOT NULL) AS 'Column1',
(SELECT TOP 1 Col2 FROM  TestTable WHERE Col2 IS NOT NULL) AS 'Column2',
(SELECT TOP 1 Col3 FROM  TestTable WHERE Col3 IS NOT NULL) AS 'Column3',
(SELECT TOP 1 Col4 FROM  TestTable WHERE Col4 IS NOT NULL) AS 'Column4'
From TestTable

Example 01 

Col1    Col2   Col3  Col4
----- ------ --------------- 
1      NULL    NULL  NULL
NULL   2       NULL  NULL
NULL   NULL    3     NULL
NULL   NULL    NULL  4

Result

Column1 Column2 Column3 Column4
-------------------------------
1       2       3       4    

Example 02

Col1    Col2   Col3  Col4
----- ------ --------------- 
1      NULL    NULL  NULL
NULL   2       NULL  2
5      NULL    3     NULL
NULL   NULL    NULL  4

Result

Column1 Column2 Column3 Column4
-------------------------------
1       2       3       2