0

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  
1
  • 1
    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.
    – jpmc26
    Apr 25 '17 at 0:48
4

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;
2
  • 1
    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.
    – BradC
    Apr 24 '17 at 20:27
  • @BradC exactly, but there's no enough information in the sample data
    – Lamak
    Apr 24 '17 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.