使用UNION ALL运算符忽略null获取结果

Question

这是我的代码：

select TRANSACTIONID, INFORMATION as "C_cntct", NULL as "C_nam"
from RETAILTRANSACTIONINFOCODETRANS as t
where INFOCODEID = 1009 and TRANSDATE = '2022-07-20'
UNION
select TRANSACTIONID, NULL, INFORMATION
from RETAILTRANSACTIONINFOCODETRANS as t
where INFOCODEID = 1010 and TRANSDATE = '2022-07-20'
group by TRANSACTIONID, INFORMATION
order by TRANSACTIONID, INFORMATION desc

我得到的结果是：

流氓阿里

TRANSACTIONID	C_cnct	C_nam
ST-EMP-ST-LHR-01-66079	空
ST-EMP-ST-LHR-01-66079	空	0321-9439143
ST-EMP-ST-LHR-01-66080	SADAFSEHAR	空
ST-EMP-ST-LHR-01-66080	空	0345-4036448

听起来你需要条件聚合(又名Pivot(，尽管没有样本输入数据，很难说。

通常只使用MIN(CASE WHEN，而不是使用显式的PIVOT语法更简单，因为后者非常有限。

SELECT
trans.TRANSACTIONID,
MIN(CASE WHEN trans.INFOCODEID = 1009 THEN trans.INFORMATION END) AS C_cntct,
MIN(CASE WHEN trans.INFOCODEID = 1010 THEN trans.INFORMATION END) AS C_nam
FROM RETAILTRANSACTIONINFOCODETRANS AS trans
WHERE trans.INFOCODEID IN (1009, 1010)
AND trans.TRANSDATE = '2022-07-20'
GROUP BY
trans.TRANSACTIONID
ORDER BY
trans.TRANSACTIONID;