Linq Pivot Query on Column with Unknown Values

Question

Say I have the following class:

public class Sightings 
{
   public string CommonName { get; set; }
   public string ScientificName { get; set; }
   public string TimePeriod { get; set; }
   public bool Seen { get; set; }
}

I would like to create a pivot query on TimePeriod. If I knew what the values in TimePeriod were ahead of time I would know how to write the query. However, I don't know. They could be years (e.g., 2007, 2008, 2009 etc.) or they could be months (e.g., Jan-2004, Feb-2004, March-2004) or they could be quarters (e.g., Q1-2004, Q2-2005, Q3-2004 etc.). It really depends on how the user wants to view the data.

Is it possible to write a Linq query that can do this, when the values in the pivot column are not known?

I've considered writing the values to an Excel sheet, creating a pivot sheet and then reading the values out of the sheet. I may have to do that if a Linq query won't work.

This looks like a result class. What do the source data look like? — Gert Arnold, Nov 02 '14 at 19:26
@GertArnold - I mentioned in the post what the most important column (TimePeriod) might contain. This is the column I want to pivot on. — Randy Minder, Nov 03 '14 at 00:37

score 0 · Accepted Answer · edited May 23 '17 at 12:05

Do you know the most granular values that the user can choose ?
If for example it's months, then you can simply write a query for months (see Is it possible to Pivot data using LINQ?).
If the user then chooses years, it should be simple to aggregate those values to years.
If they want quarters, then you can aggregate to quarters and so on.
It's maybe not the best/most generic solution, but it should get the job done quite easily.

Linq Pivot Query on Column with Unknown Values

1 Answers1