SQL, need to transform a table

Question

I'm working with Microsoft SQL Server 2014. I have a table with next data format:

|customerId | action | amount | isSuccessful | paymentCount |
|____1____  |__ W__  |____10__|____0____     |_____2________|
|____1____  |__ D__  |____20__|____1____     |_____3________|
|____1____  |__ W__  |____30__|____1____     |_____1________|
|____1____  |__ D__  |____40__|____0____     |_____1________|

What I need is to do report with this format:

|customerId|depositAmount|withdrawalAmount|depositAttemptCount|withdrawalAttemptCount|
|____1____ |______20 ____|________30_____ |________4_______  _|________3__________ |

How it's possible to 'transform' table with select?

I would appreciate any help.

Have a look here: http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — IngoB, Apr 01 '17 at 21:03

Gurwinder Singh · Accepted Answer · 2017-04-01T19:50:02.073

You can use conditional aggregation here:

select customerId,
    sum(case when action = 'D' and isSuccessful = 1 then amount else 0 end) as depositAmount,
    sum(case when action = 'W' and isSuccessful = 1 then amount else 0 end) as withdrawalAmount,
    sum(case when action = 'D' then paymentCount else 0 end) as depositAttemptCount,
    sum(case when action = 'W' then paymentCount else 0 end) as withdrawalAttemptCount
from your_table
group by customerId;

SQL, need to transform a table

1 Answers1