CODEWITHSUNDEEP
sqlsql-servert-sql
Chris Maggiulli
Chris Maggiulli

Reputation: 3814

Turning multiple rows into single row based on ID, and keeping null values

I have tried some of the various solutions posted on Stack for this issue but none of them keep null values (and it seems like the entire query is built off that assumption).

I have a table with 1 million rows. There are 10 columns. The first column is the id. Each id is unique to "item" (in my case a sales order) but has multiple rows. Each row is either completely null or has a single value in one of the columns. No two rows with the same ID have data for the same column. I need to merge these multiple rows into a single row based on the ID. However, I need to keep the null values. If the first column is null in all rows I need to keep that in the final data.

Can someone please help me with this query I've been stuck on it for 2 hours now. 

id - Age - firstname - lastname
1    13      null      null
1    null     chris    null

should output

1 13 chris null

Upvotes: 0

Views: 5819

Answers (3)

Langosta
Langosta

Reputation: 497

As some others have mentioned, you should use an aggregation query to achieve this.

select t1.id, max(t1.col1), max(t1.col2)
from tableone t1
group by t1.id

This should return nulls. If you're having issues handling your nulls, maybe implement some logic using ISNULL(). Make sure your data fields really are nulls and not empty strings.

If nulls aren't being returned, check to make sure that EVERY single row that has a particular ID has ONLY nulls. If one of them returns an empty string, then yes, it will drop the null and return anything else over the null.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269593

It sounds like you want an aggregation query:

select id, max(col1) as col1, max(col2) as col2, . . .
from t
group by id;

If all values are NULL, then this will produce NULL. If one of the rows (for an id) has a value, then this will produce that value.

Upvotes: 5

Randy
Randy

Reputation: 16677

select id, max(col1), max(col2).. etc
from mytable
group by id

Upvotes: 3

Related Questions