mysql using if and case statement to generate

Question

i have here a table name table1

debit  credit    groups

100               group1
500     -100      group3
        -100      group3
200     -50       group2

I need an output like this

group1     group2       group3
100         200           500
            -50          -100
                         -100

Here's my query

SELECT
IF((SELECT CASE WHEN groups = "group1"
THEN Debit ELSE Credit END),Debit,Credit) as group1,
IF((SELECT CASE WHEN groups = "group2"
THEN Debit ELSE Credit END),Debit,Credit) as group2,
IF((SELECT CASE WHEN groups = "group3"
THEN Debit ELSE Credit END),Debit,Credit) as group3
FROM table1

the output i get is

group1     group2       group3
    100         200           500

See http://stackoverflow.com/questions/7674786/mysql-pivot-table — Norbert, Dec 02 '15 at 02:17
I don't see a clear question here but I would suggest, based on your desired output, that you need to pivot your data. Search on "MySQL pivot data". — tale852150, Dec 02 '15 at 02:41

score 1 · Accepted Answer · answered Dec 02 '15 at 04:37

Try separating queries for credit and debit info and using union all to merge these columns into one grouped by 'group' value.

select
    case when groups = "group1" then credit end as group1,  
    case when groups = "group2" then credit end as group2,
    case when groups = "group3" then credit end as group3
from table1

union all

select
    case when groups = "group1" then debit end as group1,  
    case when groups = "group2" then debit end as group2,
    case when groups = "group3" then debit end as group3
from table1

Great! Give an update as a comment. Always interested in feedback. — openwonk, Dec 02 '15 at 07:53

mysql using if and case statement to generate

1 Answers1