tricky data pivot problem

So I've come at this a couple of ways, but all of my solutions look awful, and I'm betting there's a (more) elegant way to do this. I'm sure there's an array jocky who knows how to do this elegantly. Notice that there are a couple of data points missing in the list of times (john was out tues and mark monday, slackers...).

I'm using MySQL and Ruby 1.8.6

any help appreciated,

-s

I'm trying to turn this:

Name Day Time john mon 8 john wed 7 john thu 9 john fri 7 susan mon 8 susan tue 8 susan wed 9 susan thu 7 susan fri 7 mark tue 6 mark wed 5 mark thu 6 mark fri 5

Into this:

Job #6   mon tue wed thu fri sat sun total john 8 - 7 8.5 7 - - 30.5 susan 8 8 8.5 7 7 - - 38.5 mark - 6 5 6 5 - - 22

here's an excel file with the data (before and after) in it if that's useful...

Attachments: http://www.ruby-forum.com/attachment/1997/dataPivotSample.xls

So I've come at this a couple of ways, but all of my solutions look awful, and I'm betting there's a (more) elegant way to do this. I'm sure there's an array jocky who knows how to do this elegantly. Notice that there are a couple of data points missing in the list of times (john was out tues and mark monday, slackers...).

I'm using MySQL and Ruby 1.8.6

any help appreciated,

-s

I'm trying to turn this:

Name Day Time john mon 8

[...]

mark fri 5

I'm going to assume that you have these as ActiveRecord objects with name, day, and time fields and you've already loaded the relevant ones into an array called hours.

Into this:

Job #6   mon tue wed thu fri sat sun total john 8 - 7 8.5 7 - - 30.5 susan 8 8 8.5 7 7 - - 38.5 mark - 6 5 6 5 - - 22

DayMap = {   'mon' => 0,   'tue' => 1,   'wed' => 2,   'thu' => 3,   'fri' => 4,   'sat' => 5,   'sun' => 6   } worker_hours = hours.inject({}) { |h,entry|   (h[entry.name] ||= [0]*7)[DayMap[entry.day]] = entry.time; h   }.map { |name,times|     sum = times.inject { |s,n| s+n }     [ name, times.map { |v| v.zero? ? '-' : v }, sum ].flatten   }

--Greg