Multiple columns in one single row mysql

All we need is an easy explanation of the problem, so here it is.

My returned query looks like this

Name Type 1 Type 2 Type 3
John 1
John 3
John 5
Ronn 11
Ronn 9
Ronn 7

but I want them to be in a single row that looks like this, what functions should I use?

Name Type 1 Type 2 Type 3
John 1 3 5
Ronn 11 9 7

# # Here’s my query

SELECT c.name, 
       if(t.type = A, SUM(t.amount), "") AS Type 1,
       if(t.type = B, SUM(t.amount), "") AS Type 2,
       if(t.type = C, SUM(t.amount), "") AS Type 3
FROM 
       customer AS c,
       transaction as t

WHERE
       c.id = t.id
       AND t.type IN ("A", "B", "C")

GROUP BY c.id, t.type
    


How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is certainly a job for StackOverflow. That said …

SELECT c.name, 
       SUM(CASE WHEN t.type = 'A' THEN t.amount ELSE NULL END) as `Type 1`,
       SUM(CASE WHEN t.type = 'B' THEN t.amount ELSE NULL END) as `Type 2`,
       SUM(CASE WHEN t.type = 'C' THEN t.amount ELSE NULL END) as `Type 3`
  FROM customer AS c INNER JOIN transaction as t ON c.id = t.id
 WHERE t.type IN ('A', 'B', 'C')
 GROUP BY c.name;

This is the query you are looking for.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply