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

Question

如何将多行合并为一行，以便一行中的某些列替换与其合并的另一行中相同列中的空值？这是我所拥有的以及我正在努力实现的目标的示例。查询是:

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 |

Answer 1

我想你几乎做对了。

只需在每个字段中添加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