在SQL中对列的不同值进行分组/聚合

DB is SQL Server 2008

DB是SQL Server 2008

Table A:

表A：

Values in 'Identifier' Column: A, B, C, D, E, F Values in 'Balances' Column: 10, 100, 30, 50, 70, 55

“标识符”列中的值：“余额”列中的A，B，C，D，E，F值：10,100,30,50,70,55

Desired Result Set:

期望的结果集：

Values in 'Identifier_New' Column: Z, Y, X Values in sum(Balances) Column: 40, 170, 105

'Identifier_New'列中的值：Z，Y，X总和值（余额）列：40,170,105

Logical map to sum balances:

总和余额的逻辑映射：

sum and group balances of Identifiers A and C as Z. sum and group balances of Identifiers B and E as Y. sum and group balances of Identifiers D and E as X.

标识符A和C的总和和组余额为Z.标识符B和E的总和和组余额为Y.标识符D和E的总和和组余额为X.

Can I get the above desired result set in a single sql?

我可以在单个sql中获得上述所需的结果集吗？

Thanks

谢谢

2 个解决方案

#1

Try this:

尝试这个：

SELECT x.v AS Identifier_New, SUM(Balances)
FROM mytable
CROSS APPLY (SELECT CASE Identifier
                      WHEN 'A' THEN 'Z'
                      WHEN 'C' THEN 'Z'
                      WHEN 'B' THEN 'Y'
                      WHEN 'E' THEN 'Y'
                      WHEN 'D' THEN 'X'
                      WHEN 'F' THEN 'X'
                    END ) AS x(v)
GROUP BY x.v

Demo here

在这里演示

#2

SELECT CASE WHEN Identifier = 'A' or Identifier = 'C' THEN 'Z'
    WHEN Identifier = 'B' or Identifier = 'E' THEN 'Y'
    WHEN Identifier = 'D' or Identifier = 'F' THEN 'X' 
    END AS [Identifier_New],
    SUM(Balances) AS [Sum of Balances]
FROM TABLE A
GROUP BY CASE WHEN Identifier = 'A' or Identifier = 'C' THEN 'Z'
    WHEN Identifier = 'B' or Identifier = 'E' THEN 'Y'
    WHEN Identifier = 'D' or Identifier = 'F' THEN 'X' 
    END

#1