SQL Server - 将5个查询合并到一个表中

Hello I have to create a report on the amount of Outbound Usages from Pharmacies There are 4 types of outbound types: SMS, Call, Email, and P

您好我必须创建一份关于药房出境使用金额的报告有4种出境类型:短信,电话,电子邮件和P

I would like to get the total for each type and their sum for each pharmacy.

我想获得每种药房的总额和每种药房的总和。

Here are my queries so far.

这是我到目前为止的疑问。

select PharmacyID, count(*) as total
from OutboundCallData
where datepart(year, DateReceived) = 2014 and datepart(month, datereceived) = 1
        and (NotificationMode = 'sms' or NotificationMode = 'call' or NotificationMode = 'email' or NotificationMode = 'p')
group by PharmacyID

select PharmacyID, count(*) as sms
from OutboundCallData
where datepart(year, DateReceived) = 2014 and datepart(month, datereceived) = 1
        and NotificationMode = 'sms'
group by PharmacyID

select PharmacyID, count(*) as call
from OutboundCallData
where datepart(year, DateReceived) = 2014 and datepart(month, datereceived) = 1
        and NotificationMode = 'call'
group by PharmacyID


select PharmacyID, count(*) as email
from OutboundCallData
where datepart(year, DateReceived) = 2014 and datepart(month, datereceived) = 1
        and NotificationMode = 'email'
group by PharmacyID


select PharmacyID, count(*) as p
from OutboundCallData
where datepart(year, DateReceived) = 2014 and datepart(month, datereceived) = 1
        and NotificationMode = 'p'
group by PharmacyID

I would like to combine all of these into one table that looks like this

我想将所有这些组合成一个看起来像这样的表

PharmacyID| SMS | Call | Email | P | Total
-----------------------------------------
999000001 |  3  |  4   |   5   | 6 |  18
999000002 |  12 |  0   |   14  | 8 |  34

Displaying 0 or null would be fine.

显示0或null就可以了。

2 个解决方案

#1

A simple cross tab will work for this:

一个简单的交叉表将适用于此:

select PharmacyID,
    count(*) as total,
    count(case when NotificationMode = 'sms' then 1 else null end) as sms,
    count(case when NotificationMode = 'email' then 1 else null end) as email,
    count(case when NotificationMode = 'call' then 1 else null end) as call,
    count(case when NotificationMode = 'p' then 1 else null end) as p
from OutboundCallData
where datepart(year, DateReceived) = 2014 and datepart(month, datereceived) = 1
        and (NotificationMode = 'sms' or NotificationMode = 'call' or NotificationMode = 'email' or NotificationMode = 'p')
group by PharmacyID

Alternately, you can use a PIVOT:

或者,您可以使用PIVOT:

SELECT PharmacyID, [sms], [email], [call], [p]
FROM
    (SELECT PharmacyID, NotificationMode 
        from OutboundCallData) AS outboundData
    PIVOT
    (
       count(NotificationMode )
       FOR NotificationMode IN ([sms], [email], [call], [p])
    ) AS P

#2

Here is an example using the PIVOT keyword.

以下是使用PIVOT关键字的示例。

SELECT PharmacyID, [sms], [email], [call], [p]
FROM
    (SELECT PharmacyID, NotificationMode 
        from OutboundCallData
        where datepart(year, DateReceived) = 2014 and datepart(month, datereceived) = 1) o
    PIVOT
    (
       count(NotificationMode )
       FOR NotificationMode IN ([sms], [email], [call], [p])
    ) AS [PivotTable]

#1