SQL Server : I need to combine multiple rows into one row with different column values

Question

I have a view

Order Drink Entree Desert 
--------------------------------------------------
12 Null Preparing Null 
12 Ready NULL NULL 
12 NULL NULL Waiting 

I want to convert it to:

Order Drink Entree Desert 
---------------------------------------------------
12 Ready Preparing Waiting 

• jpmc26: I know this isn't the question you asked, but why on Earth do you have only one column with a non-NULL value on each row? Something is very wrong with the design that created this mess. Change the INSERTing code as soon as possible.

Answer 1

You can use an appropriate aggregation function. In this case, it seems like MIN or MAX will do the trick:

SELECT Order,
 MIN(Drink) Drink,
 MIN(Entree) Entree,
 MIN(Desert) Desert
FROM dbo.YourTable
GROUP BY Order;

• BradC: That's what I would have done as well. If, of course, you might have more than one non-null row for the same order, then you'd have to figure out how to reconcile that.
• Lamak: @BradC exactly, but there's no enough information in the sample data