如何将列(a)和(b)列分为b和b2

Question

现有数据。EMP001在2017年有两个记录。我想转换为1行，其中列总和(a(和列分为两个列。

EMP_NO  |   A       |   B       |   C
--------|-----------|-----------|---------   
EMP001  |   2000    |   200     |   2017A
EMP001  |   3000    |   39      |   2017B
EMP002  |   50      |   20      |   2017B

我想在下面获得结果。

EMP_NO  |   A       |   B       |   B2
--------|-----------|-----------|---------
EMP001  |   5000    |   200     |   39      
EMP002  |   50      |   20      |   0   

Answer 1

如果仅需两个列：

SELECT SUM(A) AS A,
       MAX(B) AS B,
       CASE WHEN COUNT(*) = 2 THEN MIN(B) ELSE 0 END AS B2
  FROM EMP001
 GROUP BY emp_no

Answer 2

我使用CTE，等级，联合统计来组合正确的行。第一个联合块收集一列，第二个B列，最后一个B2列。CTE仅用于提高可读性。

    ;WITH CTE(EMP_no, B, RNK)
    AS
    (
        SELECT  EMP_no, 
                B,                  
                RANK() OVER (PARTITION BY EMP_no  ORDER BY  rn ) AS RNK 
        FROM    (
                    SELECT  ROW_NUMBER() OVER (ORDER BY EMP_no ) as RN, 
                            EMP_no,
                            b                               
                    FROM    Test ) AS X             
    )
    SELECT  EMP_no,
            SUM(A) AS A,
            SUM(B) AS B,
            SUM(B2) AS B2
    FROM    (
                SELECT  EMP_no, 
                        A,
                        0 AS B,
                        0 AS B2
                FROM    Test
                    UNION ALL
                SELECT  EMP_no,
                        0,
                        CTE.B,
                        0
                FROM    CTE 
                WHERE CTE.RNK = 1
                    UNION ALL
                SELECT  EMP_no,
                        0,
                        0,
                        CTE.B
                FROM    CTE 
                WHERE CTE.RNK = 2
            ) AS X
    GROUP BY x.EMP_no

Answer 3

使用枢轴表获取结果

SELECT emp_no, sum(a),sum(isnull([2017],0)) as b ,sum(isnull([2017b],0))   
as b1
FROM 
(SELECT emp_no,a,b,c
 FROM test ) ps
 PIVOT
       (
          max(b) FOR c
                IN
                  ( [2017],[2017b])
       ) AS pvt
 group by emp_no;