Combine multiple rows into one row to replace null values in columns

Question

How can I combine multiple rows into one row so that some of the columns in one row replace null values in the same columns in the other row it is being merged with? Here’s an example of what I have and what I’m trying to achieve. The query is:

SELECT  Drug. Name,
        DefaultVendor.Name,
        Drug.Strength,
        Catalog.DIN,
        Catalog.PackSize,
        “Vendor1 Price” = CASE WHEN Ven.Name = ‘Vendor1’ THEN Catalog.Price ELSE NULL END,
        “Vendor1 ItemNum” = CASE WHEN Ven.Name = ‘Vendor1’ THEN Catalog.ItemNum ELSE NULL END,
        “Vendor2 Price” = CASE WHEN Ven.Name = ‘Vendor2’ THEN Catalog.Price ELSE NULL END,
        “Vendor2 ItemNum” = CASE WHEN Ven.Name = ‘Vendor2’ THEN Catalog.ItemNum ELSE NULL END
FROM    Catalog INNER JOIN
        Drug ON Catalog.DIN = Drug.DIN INNER JOIN
        Vendor AS Ven ON Ven.ID = Catalog.VendorID LEFT JOIN
        Vendor AS DefaultVendor ON DefaultVendor.ID = Catalog.DefVendorID OR (DefaultVendor.ID IS NULL)
WHERE   Catalog.Description LIKE ‘Acetaminophen%’
GROUP BY    Ven.Name,
            Drug.Name,
            Drug.Strength,
            Catalog.DIN,
            Catalog.PackSize,
            Catalog.Price,
            Catalog.ItemNum
ORDER BY    Drug.Strength

The result this spits out looks like this:

|          Name | DefaultVendor | Strength | DIN | PackSize | Vendor1Price | Vendor1ItemNum | Vendor2Price | Vendor2ItemNum |
|---------------|---------------|----------|-----|----------|--------------|----------------|--------------|----------------|
| Acetaminophen |       Vendor1 |    325mg |   1 |      100 |            5 |           1234 |       (null) |         (null) |
| Acetaminophen |       Vendor1 |    325mg |   1 |      200 |            9 |           1235 |       (null) |         (null) |
| Acetaminophen |       Vendor1 |    325mg |   1 |      100 |       (null) |         (null) |         5.25 |           1111 |
| Acetaminophen |       Vendor1 |    325mg |   1 |      200 |       (null) |         (null) |           10 |           1122 |
| Acetaminophen |       Vendor1 |    500mg |   2 |      100 |            7 |           1236 |       (null) |         (null) |
| Acetaminophen |       Vendor1 |    500mg |   2 |      200 |           13 |           1237 |       (null) |         (null) |
| Acetaminophen |       Vendor1 |    500mg |   2 |      100 |       (null) |         (null) |          7.5 |           1133 |
| Acetaminophen |       Vendor1 |    500mg |   2 |      200 |       (null) |         (null) |           14 |           1144 |

So I know the data is there. What I want to do is combine the rows with the same strength and packsize so that a single row will display the price and item number from both vendors. Below is the result I am looking for:

|          Name | DefaultVendor | Strength | DIN | PackSize | Vendor1Price | Vendor1ItemNum | Vendor2Price | Vendor2ItemNum |
|---------------|---------------|----------|-----|----------|--------------|----------------|--------------|----------------|
| Acetaminophen |       Vendor1 |    325mg |   1 |      100 |            5 |           1234 |       (null) |         (null) |
| Acetaminophen |       Vendor1 |    325mg |   1 |      200 |            9 |           1235 |       (null) |         (null) |
| Acetaminophen |       Vendor1 |    325mg |   1 |      100 |       (null) |         (null) |         5.25 |           1111 |

You have to explain your merge logic. Also try to include the data as text, because we cant copy / paste from pictures. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Feb 24, 2016 at 19:32
I added the data in plain text instead of the photos but it was virtually unreadable as it wasn't in a proper "table" format and it looks like I can't use HTML tables to format the data that way. EDIT: Ctrl-K trick worked. — David Flynn
– David Flynn, Commented Feb 24, 2016 at 19:44

Juan Carlos Oropeza · Accepted Answer · 2016-02-24 19:35:43Z

4

I think you almost got it right.

Just add MAX() to each field

“Vendor1 Price” = MAX(CASE WHEN Ven.Name = ‘Vendor1’ THEN Catalog.Price END),
“Vendor1 ItemNum” = MAX(CASE WHEN Ven.Name = ‘Vendor1’ THEN Catalog.ItemNum END),
“Vendor2 Price” = MAX(CASE WHEN Ven.Name = ‘Vendor2’ THEN Catalog.Price END),
“Vendor2 ItemNum” = MAX(CASE WHEN Ven.Name = ‘Vendor2’ THEN Catalog.ItemNum END)

Also not need include ELSE NULL by default if CASE doesnt found a match will return NULL

answered Feb 24, 2016 at 19:35

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Flynn Over a year ago

I added MAX to those columns and removed the "ELSE NULL" but I'm still getting the exact same result

David Flynn Over a year ago

After some playing around, I got it. I forgot I had to remove the Price and ItemNum from the group by clause after adding the MAX function.

Juan Carlos Oropeza Over a year ago

Glad you solve it. Remember the upvote. Next time you can also use this tool sqlfiddle.com/#!15/36069/1 here you can load your schema and use the button TEXT TO DDL to easy setup a sample db

Juan Carlos Oropeza Over a year ago

Also have this to make your table preaty sensefulsolutions.com/2010/10/format-text-as-table.html

Fuzzy · Accepted Answer · 2016-02-24 19:39:28Z

0

This should do it:

SELECT  Drug. Name,
        DefaultVendor.Name,
        Drug.Strength,
        Catalog.DIN,
        Catalog.PackSize,
        'Vendor1 Price' = SUM(CASE WHEN Ven.Name = 'Vendor1' THEN Catalog.Price  END),
        'Vendor1 ItemNum' = SUM(CASE WHEN Ven.Name = 'Vendor1' THEN Catalog.ItemNum  END),
        'Vendor2 Price' = SUM(CASE WHEN Ven.Name = 'Vendor2' THEN Catalog.Price  END),
        'Vendor2 ItemNum' = SUM(CASE WHEN Ven.Name = 'Vendor2' THEN Catalog.ItemNum  END)
FROM    Catalog INNER JOIN
        Drug ON Catalog.DIN = Drug.DIN INNER JOIN
        Vendor AS Ven ON Ven.ID = Catalog.VendorID LEFT JOIN
        Vendor AS DefaultVendor ON DefaultVendor.ID = Catalog.DefVendorID OR (DefaultVendor.ID IS NULL)
WHERE   Catalog.Description LIKE 'Acetaminophen%'
GROUP BY    Ven.Name,
            Drug.Name,
            Drug.Strength,
            Catalog.DIN,
            Catalog.PackSize,
            Catalog.Price,
            Catalog.ItemNum
ORDER BY    Drug.Strength

answered Feb 24, 2016 at 19:39

Fuzzy

3,8102 gold badges17 silver badges34 bronze badges

1 Comment

David Flynn Over a year ago

That doesn't work. I get the same amount of rows but with inaccurate prices and item numbers because of the SUM function.

Collectives™ on Stack Overflow

Combine multiple rows into one row to replace null values in columns

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related