MySQL Select single column into multiple column

Question

So, I have the following table (gross example):

CREATE TABLE budget_details(
    detail_id INT PRIMARY KEY,
    budget_id INT FOREIGN KEY,
    detail_month VARCHAR(16),
    total DECIMAL(18,2)
)

I'm trying to Select a Single Row with budget_id and have different columns for each Month (eg. January, February, etc) displaying their total, and all the other months null, showing 0, idc, something like this Example of results

I know it'd be easier to just recreate the table, but it's an old software and i'd break a lot of stuff

Any and all help is appreciated

Adrian · Accepted Answer · 2022-02-23 01:40:02Z

2

The query below provides the result as shown in your image.

 SELECT 
   detail_id,
   budget_id, 
  CASE WHEN detail_month = 'January' THEN total END AS 'January',
  CASE WHEN detail_month = 'February' THEN total END AS 'February',
  CASE WHEN detail_month = 'March' THEN total END AS 'March'
FROM 
 budget_details
ORDER BY
   detail_id,
   budget_id

answered Feb 23, 2022 at 1:40

Adrian

4524 silver badges16 bronze badges

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

3 Comments

Gerardo Sanchez Over a year ago

Not quite the answer, because I'm just realizing I didn't post my question quite right, I get multiple rows per detail_id, each per month, i want a single row with all months, but i'll try to work it out from there, Thanks a lot!

Gerardo Sanchez Over a year ago

Just aded SUM outside of the CASE method, and used GROUP BY, and got my full answer, again thanks a lot!

Adrian Over a year ago

No worries - good to see you're on the right track

Collectives™ on Stack Overflow

MySQL Select single column into multiple column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related