Attempt pivot query results onto an object, using linq or otherwise
===

I have a query that returns data like the following:

    Event | Sum | Date 
    sent  | 400 | 1/1/12 
    open  | 200 | 1/1/12
    click | 50  | 1/1/12 
    sent  | 300 | 1/8/12
    open  | 150 | 1/8/12
    click | 30  | 1/8/12

To send these values to google visualization api to render a graph I need to arrange the values like this:

    ['date' , 'sent' , 'opened' , 'clicked']
    ['1/1/12', '400' , '200' , '50']
    ['1/8/12', '300', '150' , '30']

I'm not really familiar with this stuff, but to me that is pivoting by date. 

The only solution I could think of (I'm on a tight deadline with a new language, C#, and I just did the best I could... I'm also not a programmer, just a data analyst...).

At first I tried to find a way to make the linq query fill the object pivoted the way I needed, but I couldn't figure that out. If someone could suggest a way to solve this problem using linq alone, I would be greatly appreciated. 

My solution resulted in the linq query just performing the SUM() on THECOUNT values, returning the object as defined above, and then using 2 arrays to iterate through the resultset and pivot the data, a manual transformation into a format that can easily be iterated through with a foreach when it is time to construct the JSON string that will be passed to the view.

Here is the linq query I used:

        var dbLinqObj= from r in db.SEND_OPEN_CLICK_COUNTS
                    group r by new { r.EVENT, r.RUNDATE } into g
                    select new {
                        EVENT = g.Key.EVENT,
                        THECOUNT = g.Sum(r => r.THECOUNT),
                        RUNDATE = g.Key.RUNDATE
                        
                    } ;
        dbLinqObj= dbLinqObj.OrderBy(r => r.RUNDATE);

And this is the code I made to transform the above LINQ object into an array, whose structure has been pivoted on date.


    // to instantiate the pivot array I divide the length by the number of columns
    // this instantiates array with the expected length. If we selected more columns
    // I would need to adjust the denominator 
    object[] pivotArray = new object[dbLinqObj.Count()/3];

    // each row has 4 fields (date, send, open, click):
    object[] pivotRow = new object[4];

    // instantiate counters to enforce the structure of the array objects:
    int columnCount = 1; 
    int resultCount = 0;

    // loop through each record in the linq object:
    foreach (var rs in dbLinqObj)
                {
                    // transform the rows based on the column structure of the new pivotArray
                    // since I know that there are 3 attributes returned per date,
                    

                    if (columnCount == 1)
                    {
                        // fill the appropriate array position (via resultCount) with an array of row values
                        pivotRow[0] = rs.RUNDATE;
                        pivotRow[1] = rs.THECOUNT;
                    }
    
                    if (columnCount == 2)
                    {
                        pivotRow[2] = rs.THECOUNT;
                    }
                    if (columnCount == 3)
                    {
                        pivotRow[3] = rs.THECOUNT;
                        pivotArray[resultCount] = pivotArray;
                        resultCount++;
                        pivotArray = new object[4];
                    }
    
                    columnCount ++;
                    if (columnCount == 4) { columnCount = 1; }
                }
    

      // I then build the JSON string in the format that google visualization prefers:
 
      string jSON = "['date' , 'sent' , 'opened' , 'clicked'] , ";
      jSON += " [";
    
                foreach (object[] r in pivotArray )
                {
                    jSON += "[" + r[0] + "," + r[1] + "," + r[2] + "," + r[3] + "],";
                }
    
      int l = jSON.Length;
      jSON = jSON.Substring(0, l - 1);
      jSON += "]";
      
      // this returns the [HttpPost] public JsonAction sendOpenClickCounts()
      return Json(jSON, "application/json");


I'm asking for a suggestion on how to better solve this problem. The problem being that I need to pivot on a field returned from a database (because I'm not familiar with how to pivot data via SQL or LINQ). 

I believe my solution is very CPU intensive and ugly and a host of other bad things, including being rigidly tied to the structure of the datasource.

Anywho, thoughts, comments, suggestions, criticism are all welcome. thanks!