How do I format a mysql table this way?

Question

Say I have a table like this:

emp_id  work_site   expenses
001     atlanta     $500
001     atlanta     $750
002     boston      $750
002     chicago     $250
003     chicago     $500
003     chicago     $500

And now I want to group expenses by emp_id and unique city, in this format:

emp_id  atlanta boston  chicago
001     $1250    $0     $0
002     $0       $750   $250
003     $0       $0     $1000

What's the best way to do this in mysql or PHP?

Take a look at [this](http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql). — Kermit, Sep 02 '12 at 06:28

score 3 · Answer 1 · answered Sep 02 '12 at 06:33

3

you can pivot using this, you need to convert the expenses to numeric first if its not

 select emp_id
,SUM(CASE  WHEN work_site ='atlanta' THEN expenses else 0 END) as 'atlanta'
,SUM(CASE  WHEN work_site ='boston' THEN expenses else 0 END) as 'boston'
,SUM(CASE  WHEN work_site ='chicago' THEN expenses else 0 END) as 'chicago'
from Table1
group by emp_id

SQL FIDDLE http://sqlfiddle.com/#!2/dd535/1/0

answered Sep 02 '12 at 06:33

ClearLogic

3,616
1
23
31

Thank you for taking the time! – user1638110 Sep 02 '12 at 07:07
I can't find a button for "accepting". And by upvote, I take it you mean the yes button on "was this post useful to you"? – user1638110 Sep 02 '12 at 08:07

score 3 · Answer 2 · answered Sep 02 '12 at 06:59

Simply , you can do like this:

  SELECT emp_id, 
         SUM( if( work_site = 'atlanta', expenses , 0 ) ) AS 'atlanta',  
         SUM( if( work_site = 'boston', expenses , 0 ) ) AS 'boston', 
         SUM( if( work_site = 'chicago', expenses , 0 ) ) AS 'chicago' 
  FROM your_table
  GROUP BY emp_id

How do I format a mysql table this way?

2 Answers2