# tricky data pivot problem

**URL:** <https://discuss.rubyonrails.org/t/tricky-data-pivot-problem/29728>\
**Category:** rubyonrails-talk\
**Created:** [May 23, 2008, 5:28am UTC](https://discuss.rubyonrails.org/t/tricky-data-pivot-problem/29728 "2008-05-23T05:28:18Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [May 23, 2008, 5:28am UTC](https://discuss.rubyonrails.org/t/tricky-data-pivot-problem/29728/1 "2008-05-23T05:28:18Z")

</div>

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 &nbsp;&nbsp;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

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [May 23, 2008, 5:30am UTC](https://discuss.rubyonrails.org/t/tricky-data-pivot-problem/29728/2 "2008-05-23T05:30:58Z")

</div>

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](http://www.ruby-forum.com/attachment/1997/dataPivotSample.xls)

---

<div class="post-metadata">

**Author:** ![Gregory\_Seidman1](https://avatars.discourse-cdn.com/v4/letter/g/a88e57/32.png) [@Gregory\_Seidman1](https://discuss.rubyonrails.org/u/Gregory_Seidman1)\
**Post date:** [May 23, 2008, 11:44am UTC](https://discuss.rubyonrails.org/t/tricky-data-pivot-problem/29728/3 "2008-05-23T11:44:03Z")

</div>

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

--Greg