SQL Server 2012 - I tried using 2-columns pivot but SUM function doesn't give expected result

Question

I searched for similar question, but the most similar is this one (Not getting the correct SUM values in SQL Server 2012 when using a PIVOT), still the condition is slightly different, so I'll just make new question

I wanted to make a pivot query for 2 columns, one column is money type while the other is date. Here is the example data:

| idTransaction | txtCashOutput | cashOutput | txtDateOutput | dateOutput |
| ------------- | ------------- | ---------- | ------------- | ---------- |
|           101 |        cash_1 |       2000 |        date_1 | 2020-01-01 |
|           101 |        cash_1 |       2000 |        date_1 | 2020-02-01 |
|           101 |        cash_2 |       1000 |        date_2 | 2020-03-01 |
|           101 |        cash_2 |       1200 |        date_2 | 2020-04-01 |
|           101 |        cash_3 |       1500 |        date_3 | 2020-05-01 |

I deliberately use these "txt" tables to help me set up 2-column pivots. I forgot how to use dynamic multiple pivots.

Here's my query:

select 
    idTransaction,
    cash_1 = sum(cash_1), date_1 = max(date_1),
    cash_2 = sum(cash_2), date_2 = max(date_2),
    cash_3 = sum(cash_3), date_3 = max(date_3)
from (
    select 
        idTransaction,
        txtCashOutput,
        cashOutput,
        txtDateOutput,
        dateOutput
    from Table01
) src
pivot ( 
    sum(cashOutput) FOR txtCashOutput IN (cash_1,cash_2,cash_3)  
) AS piv1
pivot (
    max(dateOutput) FOR txtDateOutput IN (date_1,date_2,date_3)
) as piv2

group by
    idTransaction

Expected result:

| idTransaction | cash_1 |     date_1 | cash_2 |     date_2 | cash_3 |     date_3 |
| ------------- | ------ | ---------- | ------ | ---------- | ------ | ---------- |
|           101 |   4000 | 2020-02-01 |   2200 | 2020-04-01 |   1500 | 2020-05-01 |

But I got this instead:

| idTransaction | cash_1 |     date_1 | cash_2 |     date_2 | cash_3 |     date_3 |
| ------------- | ------ | ---------- | ------ | ---------- | ------ | ---------- |
|           101 |   2000 | 2020-02-01 |   2200 | 2020-04-01 |   1500 | 2020-05-01 |

The SUM result of cash_1 is not addition, but its distinct value instead, while the SUM result of cash_2 is correct. I'm kind of confused on why if the value is different, the SUM result is correct, but if the value is the same, the SUM result is incorrect.

The question I mentioned above is they need to show the SUM result of one column, while making the pivots of the other column, so I guess it's a little different than mine.

I hope this is clear enough.

Any help would be greatly appreciated.

Thanks!

score 0 · Answer 1 · answered Mar 26 '21 at 12:41

it is easier to do it using conditional aggregate with CASE expression

SELECT idTransaction,
       MAX (CASE WHEN txtCashOutput = 'cash_1' THEN cashOutput END ) AS cash_1, 
       MAX (CASE WHEN txtDateOutput = 'date_1' THEN dateOutput END ) AS date_1 
       MAX (CASE WHEN txtCashOutput = 'cash_2' THEN cashOutput END ) AS cash_2, 
       MAX (CASE WHEN txtDateOutput = 'date_2' THEN dateOutput END ) AS date_2,
       . . .  
FROM   Table01
GROUP BY idTransaction

SQL Server 2012 - I tried using 2-columns pivot but SUM function doesn't give expected result

1 Answers1