SQL 转置行为列，合并多行

数据库中的表如下：

需要查询每个人去每个客户的天数，每个客户去的总天数。

SQL语句如下：

1、先计算每个客户的总天数

select distinct
'' as '姓名',
(select count( distinct dt_ReceiveTime) as 出差天数 from SMS_DateReport where vc_CustomerName='北路科技') as '北路科技',
(select count( distinct dt_ReceiveTime) as 出差天数 from SMS_DateReport where vc_CustomerName='山西煤矿' ) as '山西煤矿',
(select count( distinct dt_ReceiveTime) as 出差天数 from SMS_DateReport where vc_CustomerName='陕西煤矿' ) '陕西煤矿'
from SMS_DateReport  

2、计算每个人去每个客户的次数

SELECT 
    vc_EmployeeName, 
    count( distinct CASE  vc_CustomerName WHEN '北路科技' THEN dt_ReceiveTime else null end) , --as '北路科技',
    count( distinct CASE   vc_CustomerName WHEN '山西煤矿' THEN dt_ReceiveTime else null end), -- as '山西煤矿',
    count( distinct CASE   vc_CustomerName WHEN '陕西煤矿' THEN dt_ReceiveTime else null end) -- as '陕西煤矿'
    
FROM SMS_DateReport 
group by vc_EmployeeName

3、合并行

select distinct
'' as '姓名',
(select count( distinct dt_ReceiveTime) as 出差天数 from SMS_DateReport where vc_CustomerName='北路科技') as '北路科技',
(select count( distinct dt_ReceiveTime) as 出差天数 from SMS_DateReport where vc_CustomerName='山西煤矿' ) as '山西煤矿',
(select count( distinct dt_ReceiveTime) as 出差天数 from SMS_DateReport where vc_CustomerName='陕西煤矿' ) '陕西煤矿'
from SMS_DateReport  


union all 


SELECT 
    vc_EmployeeName, 
    count( distinct CASE  vc_CustomerName WHEN '北路科技' THEN dt_ReceiveTime else null end) , --as '北路科技',
    count( distinct CASE   vc_CustomerName WHEN '山西煤矿' THEN dt_ReceiveTime else null end), -- as '山西煤矿',
    count( distinct CASE   vc_CustomerName WHEN '陕西煤矿' THEN dt_ReceiveTime else null end) -- as '陕西煤矿'
    
FROM SMS_DateReport 
group by vc_EmployeeName

最终结果如下：

4、对于客户很多的情况，可以使用游标，生成SQL字符串，再查询。