将多行合并为一行以替换列中的空值

如何将多行合并为一行，以便一行中的某些列替换要合并的另一行中相同列中的空值？ 这是我所拥有的和我想要实现的目标的一个例子。查询是：

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

吐出的结果如下所示：

|          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 |

所以我知道数据就在那里。我想要做的是将具有相同强度和包装尺寸的行组合在一起，以便单行将显示两个供应商的价格和商品编号。以下是我正在寻找的结果：

|          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 |

我想你几乎做对了。

只需添加MAX()到每个领域

“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)

也不需要包括ELSE NULL默认情况下如果CASE未找到匹配项将返回NULL