How to get the non null values in a row sets to one row usin t-sql 2008

keplan
keplan used Ask the Experts™
on
my excel data set like below, I need to get the resut as I shown below:

Excel Source:
Col1 Col2 Col3 Col4 Col5 Col6  col7
1       abc   tt     null   88   9      null
1       abc   tt     2      null  null   null
1       abc   tt      null  null  null  null

my expected Result:

col1 col2 col3  col4 col5 col6  col7
1     abc   tt     2     88    9     null


Can anybody suggest solution to this?

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you can use max method
select MAX(col1) , MAX(col2), MAX(col3),  MAX(col4), MAX(col5) ,MAX(col6)  ,MAX(col7)

Commented:
Select Max(col1) col1, Max(col2) col2, Max(col3) col3, Max(col4) col4, Max(col5) col5, Max(col6) col6, Max(col7) col7 FROM <tablename>

Author

Commented:
thanks for all those gave me some sort of solution:
sorry for the not mentioned this bit ealier: i've added the new data into my source:

Excel Source:
Col1 Col2 Col3 Col4 Col5 Col6  col7
1       abc   tt     null   88   9      null
1       abc   tt     2      null  null   null
1       abc   tt      null  null  null  null
3       fgt    hh    null   3      null  null
3      fgt     hh    2      null   1      null
4      kkl    jj       null  8      3       1
4      kkl    jj      3     null    null   null

so as the data set, the col1, col2, col3 need to eliminated it duplicates, as below result:

col1   col2  col3 col4  col5  col6  col7
1       abc   tt     2      3       9      null
3       fgt    hh    2      3       1     null
4      kkl    jj      3       8      3       1

please give me solution to this
thanks in advance




How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

SharathData Engineer

Commented:
try this.
SELECT col1, 
         MAX(col2) col2, 
         MAX(col3) col3, 
         MAX(col4) col4, 
         MAX(col5) col5, 
         MAX(col6) col6, 
         MAX(col7) col7 
    FROM your_table 
GROUP BY col1; 

Open in new window

Commented:
Select Max(col1) col1, Max(col2) col2, Max(col3) col3, Max(col4) col4, Max(col5) col5, Max(col6) col6, Max(col7) col7 FROM <tablename>
GROUP BY col1

i dont know if excell isnull function like isnull(value,newvalue)  if it is you can do it like this  
Select DISTINCT ISNULL(col1,MAX(col1)) col1, ISNULL(col2,MAX(col2)), ISNULL(col3,MAX(col3)) as col3, ISNULL(col4,MAX(col4)) as col4, ISNULL(col5,MAX(col5)) col5, ISNULL(col6,MAX(col6)) col6, ISNULL(col7,MAX(col7)) col7 FROM <tablename>

OR if it is isnull(value) you can use case when statements
Select DISTINCT case when isnull(col1) then MAX(col1)) col1 else col1 end as col1,
case when isnull(col2)  then MAX(col2)) col2 else col2 end as col2
from xx
 

Author

Commented:
solve the problem partially

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial