在SQL Server中的所有列所需的非空值

Question

我已经记录来等作为画面

查询不工作：

SELECT 
    'Business_Details' AS 'Business Phone','Business Email' 
FROM 
    (SELECT 
     TELCOM_NAME, TELCOM_ADDRESS 
    FROM 
     [mi_s_data].[dbo].[TEMP_MOS_CONTACT] 
    ) AS SourceTable 
PIVOT 
(
MAX(TELCOM_ADDRESS) 
FOR TELCOM_NAME IN ('Business_Phone','Business_Email') 
) AS PivotTable; 

我需要一个SQL Server查询，可以给输出像所示在这张照片中：

Answer 1

也许这样的事情？

Select ... 
case when alias1.TELCOM_ADDRESS is null 
    then (select alias2.TELCOM_ADDRESS 
      from [mi_s_data].[dbo].[TEMP_MOS_CONTACT]) alias2 
      where alias1.Contact_id=alias2.Contact_id 
      and alias2.TELCOM_ADDRESS is not null) 
     ) 
    else alias1.TELCOM_ADDRESS 
    end as TELCOM_ADDRESS 
from [mi_s_data].[dbo].[TEMP_MOS_CONTACT]) alias1 

Answer 2

我只想使用条件汇聚建议：

select contact_id, 
     max(case when telcom_name = 'Business Phone' then telcom_address end) as business_phone, 
     max(case when telcom_name = 'Business Email' then telcom_address end) as business_email 
from mi_s_data.dbo.temp_mos_contact tmc 
group by contact_id;