如何在SQL中选择最常用的值组合

I have the following table in a SQL database:

我在SQL数据库中有以下表:

Lender  State   Revenue
A       AK      13
A       NJ      20
A       CA      25
B       AK      30
B       NJ      25
C       NJ      20
C       CA      31
D       CA      21

I'm looking to create a table with different state combinations and a sum of revenues:

我想创建一个具有不同状态组合和收入总和的表:

State       Revenue    Lender Count
AK, NJ, CA  58         1
AK, NJ      55         1
NJ, CA      51         1
CA          21         1

Is this do-able through SQL? If not, what's the best way?

这可以通过SQL吗?如果没有,最好的方法是什么?

1 个解决方案

#1

Try this.

SELECT LEFT(cs.State, Len(cs.State) - 1) AS State,
       Sum(Revenue)                      AS Revenue,
       1 as Leader_Count
FROM   Yourtable a
       CROSS APPLY (SELECT State + ','
                    FROM   Yourtable B
                    WHERE  a.Lender = b.Lender
                    FOR XML PATH('')) cs (State)
GROUP  BY Lender,
          LEFT(cs.State, Len(cs.State) - 1)