Multiple columns in one single row mysql

Question

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,
       t_detail AS td

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

GROUP BY c.id, t.type

customer

id = fk to t_details

transaction

amount
type

t_detail

t_id id = FK. customer id

Can you give sample data for your two tables?

Kevin
– Kevin

2021-07-12 14:00:55 +00:00
Commented Jul 12, 2021 at 14:00 — Kevin
– Kevin, Commented Jul 12, 2021 at 14:00
@Kevin see edited ty

slowdancer
– slowdancer

2021-07-12 14:11:38 +00:00
Commented Jul 12, 2021 at 14:11 — slowdancer
– slowdancer, Commented Jul 12, 2021 at 14:11

eshirvana · Accepted Answer · 2021-07-12 14:07:55Z

1

you have to provide more information ( sample data) but seems like you need to group by name:

SELECT c.name, 
       SUM(case when t.type = 'A' then t.amount end) AS Type1,
       SUM(case when t.type = 'B' then t.amount end) AS Type2,
       SUM(case when t.type = 'C' then t.amount end) AS Type3
FROM  customer AS c
inner join transaction as t
   on c.id = t.id
   AND t.type IN ("A", "B", "C")
GROUP BY c.name

also always use explicit join.

edited Jul 12, 2021 at 14:07

answered Jul 12, 2021 at 13:53

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

slowdancer Over a year ago

it still return the same table

slowdancer Over a year ago

it sums up all the values and returns to 4th column(type 3)

slowdancer Over a year ago

thanks man, problem solved! Thank you. Been trying to solve this for more than an hour now, thanks

Kevin Over a year ago

You don't actually need "AND t.type IN ("A", "B", "C")"

Collectives™ on Stack Overflow

Multiple columns in one single row mysql

My returned query looks like this

Here's my query

customer

transaction

t_detail

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

My returned query looks like this

Here's my query

customer

transaction

t_detail

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related