Query for splitting into 2 columns-CodePudding

I have the following table:

table1
----------------------------
| id | desc  | dc | amount |
----------------------------
|1   | trx 1 | d  | 100000 |
|2   | trx 2 | d  | 500000 |
|3   | trx 3 | c  | 800000 |
|4   | trx 4 | d  | 100000 |
|5   | trx 5 | c  | 900000 |
|6   | trx 6 | d  | 700000 |
----------------------------

I need to query from table1 above to have the following output :

----------------------------------
| id | desc  | d        | c      | 
----------------------------------
|1   | trx 1 | 100000   |        |
|2   | trx 2 | 500000   |        |
|3   | trx 3 |          | 800000 |
|4   | trx 4 | 100000   |        |
|5   | trx 5 |          | 900000 |
|6   | trx 6 | 700000   |        |
----------------------------------
total        | 1500000  | 1700000|
----------------------------------

Please advise what is the SQL command do be executed.

CodePudding user response：

Try this:

SELECT 
    id, desc, 
    CASE WHEN dc = 'C' THEN amount ELSE NULL END AS c,
    CASE WHEN dc = 'D' THEN amount ELSE NULL END AS D
FROM myTable

CodePudding user response：

select id, desc, case when dc='d' then amount else null end as d, case when dc='c' then amount else null end as c from table1