CODEWITHSUNDEEP
sql
fawzan
fawzan

Reputation: 214

Query for splitting into 2 columns

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.

Upvotes: 1

Views: 55

Answers (2)

Nayanish Damania
Nayanish Damania

Reputation: 652

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 table1

Upvotes: 2

nzLoop
nzLoop

Reputation: 31

I suggest you to do something like:

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

Upvotes: 1

Related Questions