sql - Count number of distinct values based on another set of distinct values

Question

I am trying to count a set of values based on another distinct value set:

-----------------------------------------------------    
|CusID  |Agent1  |Agent2 |Agent 3 |Agent 4 |Agent 5 |
-----------------------------------------------------
|1      |Pat     |Pat    |Jane    |Simon   |Jane    |
|2      |Pater   |Pat    |Simon   |Simon   |Pat     |
|1      |Jane    |Jane   |Simon   |Pat     |Pat     |
|3      |Simon   |Simon  |Jane    |Pat     |Peter   |
|3      |Jane    |Simon  |Pat     |Pater   |Pat     |
-----------------------------------------------------

I want to get:

----------------------------------------------------------------------------
|CusIDUnq  |AgentName|Count|AgentName|Count|AgentName|Count|AgentName|Count|
----------------------------------------------------------------------------
|1         |Pat      |4    |Jane     |4    |Simon    |2    |Peter    |0    |
|2         |Pat      |2    |Jane     |0    |Simon    |2    |Pater    |1    |
|3         |Pat      |3    |Jane     |2    |Simon    |3    |Peter    |2    |
----------------------------------------------------------------------------

How can I do this using SQL?

Thanks in advance!

edit: I need to tell that the number of customers and agents could change over time. So, I think a solution like below is also fine with me. The point is, I can't code the agent names or customer IDs into the query.

-----------------------
|CusID|AgentName|Count|
-----------------------
|1    |Pat      |4    |
|1    |Jane     |4    |
|1    |Simon    |2    |
|2    |Pat      |2    |
|2    |Simon    |2    |
|2    |Peter    |1    |

etc. Here, I need to omit 0 results where an agent is not listed for a specific customer.

Again, many thanks!

标签： sqlsql-serversql-server-2016counting

Answer 1

如果我理解正确，你可以这样做：

select id,
       sum(case when 'Pat' in (Agent1, Agent2, Agent3, Agent4, Agent5)
                then 1 else 0 end
           end) as num_pat,
       sum(case when 'Jane' in (Agent1, Agent2, Agent3, Agent4, Agent5)
                then 1 else 0 end
           end) as num_jane,
       sum(case when 'Simon' in (Agent1, Agent2, Agent3, Agent4, Agent5)
                then 1 else 0 end
           end) as num_simon,
       sum(case when 'Peter' in (Agent1, Agent2, Agent3, Agent4, Agent5)
                then 1 else 0 end
           end) as num_peter
from t
group by id;

这会将计数与代理名称放在列名称中，但这似乎是您想要的。