如何使用CASE子句将结果压缩为一行

Question

我正在尝试使用CASE子句，并试图将结果压缩为一行结果。现在，我的数据是这样回来的。

asset_id    ID_ISIN          ID_SEDOL     ID_CUSIP
131218      NULL             NULL         NULL
131218      NULL             2450915      NULL
131218      NULL             NULL         448814DM2
131218      NULL             NULL         448814DM2
131218      NA-000K0VF05-4   NULL         NULL
131218      NULL             2450915      NULL

我真的很想看到这个。

asset_id    ID_ISIN          ID_SEDOL     ID_CUSIP
131218      NA-000K0VF05-4   2450915      448814DM2

这是我正在测试的SQL。

SELECT CW.asset_id,
CASE WHEN CW.id_cd = 'ISN' THEN CW.id_number ELSE NULL END As ID_ISIN,
CASE WHEN CW.id_cd = 'SED' THEN CW.id_number ELSE NULL END As ID_SEDOL,
CASE WHEN CW.id_cd = 'CSP' THEN CW.id_number ELSE NULL END As ID_CUSIP
FROM CPDG As CPDG
INNER JOIN CW As CW
ON CPDG.asset_id = CW.asset_id
WHERE CW.asset_id = '131218'

Answer 1

您需要聚合：

SELECT CW.asset_id,
MAX(CASE WHEN CW.id_cd = 'ISN' THEN CW.id_number END) As ID_ISIN,
MAX(CASE WHEN CW.id_cd = 'SED' THEN CW.id_number END) As ID_SEDOL,
MAX(CASE WHEN CW.id_cd = 'CSP' THEN CW.id_number END) As ID_CUSIP
FROM CPDG CPDG INNER JOIN
CW CW
ON CPDG.asset_id = CW.asset_id
WHERE CW.asset_id = '131218'
GROUP BY CW.asset_id;

注意：我删除了ELSE NULL，因为它是多余的。此外，如果asset_id是一个数字(我怀疑它是(，那么从比较中删除单引号。