CODEWITHSUNDEEP
sqlsql-servert-sqlselectgroup-by
Nalluri
Nalluri

Reputation: 101

Merging multiple rows into one row by Grouping and checking for non null

I have data in this format. I want to merge multiple rows into one-row grouping by ID column. each row will have only one non null value and one non null value for each column when grouped by ID.

   [ID], [foo], [foo1], [foo2], [foo3], [foo4], [foo5]
    1,    data1,  null,    null,  null,    null,  null
    1,    null,   data2,    null,  null,    null,  null
    1,    null,  null,    data3,  null,    null,  null
    1,    null,  null,    null,  data4,    null,  null
    1,    null,  null,    null,  null,    data5,  null
    1,    null,  null,    null,  null,    null,  data6
    2,    data1,  null,    null,  null,    null,  null
    2,    null,  data2,    null,  null,    null,  null
    2,    null,  null,    data3,  null,    null,  null
    2,    null,  null,    null,  data4,    null,  null
    2,    null,  null,    null,  null,    data5,  null
    2,    null,  null,    null,  null,    null,  data6

Desired Output:
   [ID], [foo], [foo1], [foo2], [foo3], [foo4], [foo5]
   1,    data1,  data2,  data3, data4,   data5,  data6
   2,    data1,  data2,  data3, data4,  data5,  data6

Upvotes: 2

Views: 1832

Answers (2)

Mureinik
Mureinik

Reputation: 311163

The aggregate functions max and min (as well as most others) will just ignore nulls. You could group by the ID and query the max of the other columns, which would return the single non-null value this column has:

SELECT   id, MAX(foo), MAX(foo1), MAX(foo2), MAX(foo3), MAX(foo4), MAX(foo5)
FROM     mytable
GROUP BY id

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269603

In your case, you can use max():

select id, max(foo) as foo, max(foo1) as foo1, . . . 
from t
group by id;

I should note that your original data structure is often produced by a query that is a bit awry. Sometimes it is easier to fix the code that generates that result.

Upvotes: 3

Related Questions