How to query a list of data to make rows into columns

Question

I have a list of data like below

Item    Vendor  Compliance  Status

I1      V1          C1          Y
I1      V1          C2          N
I1      V1          C3          Y
I1      V2          C1          Y
I1      V2          C2          Y
I2      V1          C3          Y

How can I query to make it become

Item    Vendor  C1  C2  C3

I1      V1       Y  N   Y    
I1      V2       Y  Y   -
I2      V1       -  -   Y

While the compliance might have many, not necessary only have C1, C2, C3.

Answer 1

I am going to cheat of @Serv and use a part of the code he used for defining data (sigh - yeah I am lazy).

To handle the dynamic aspect of having many compliances you can use ExpandoObject. All you need to do is to get all existing compliance types and for each type assign a property to your object.

void Main()
{
    var content = new List<Content>()
    {
        new Content("I1", "V1", "C1", true),
        new Content("I1", "V1", "C2", false),
        new Content("I1", "V1", "C3", true),
        new Content("I1", "V2", "C1", true),
        new Content("I1", "V2", "C2", true),
        new Content("I2", "V1", "C3", true),
        new Content("I2", "V1", "C4", true)
    };

    var compliances = content.Select(c=>c.Compliance).Distinct();
    var temp = content.GroupBy (c => new {Item = c.Item, Vendor = c.Vendor});
    var results = temp.Select (t => 
    {
        dynamic result = new ExpandoObject();
        result.Item = t.Key.Item;
        result.Vendor = t.Key.Vendor;
        foreach(var compliance in compliances)
        {
            var isCompliant = t.Any(x => x.Compliance == compliance && x.Status.GetValueOrDefault());
            ((IDictionary<String, Object>)result).Add(compliance, isCompliant);
        }
        return result;
    }).Dump();
}

public class Content
{
    public Content(string item, string vendor, string compliance, bool? status)
    {
        Item = item; Vendor = vendor; Compliance = compliance; Status = status;
    }

    public string Item { get; set; }
    public string Vendor { get; set; }
    public string Compliance { get; set; }
    public bool? Status { get; set; }
}

And the results:

EDIT

To get null instead of false when a compliance does not exist use this code the foreach loop:

var isCompliant = t.FirstOrDefault(x => x.Compliance == compliance); 
((IDictionary<String, Object>)result).Add(compliance, isCompliant == null ? null : isCompliant.Status);

The results will look like this:

Answer 2

I am pretty sure I violated a dozen principles of good programming by doing this, but I think this solution works. What I have done is created a class in Linqpad where Status is of type bool (I am lazy).

This is the whole snippet so you can reproduce it:

void Main()
{
    var content = new List<Content>()
    {
      new Content("I1", "V1", "C1", true),
      new Content("I1", "V1", "C2", false),
      new Content("I1", "V1", "C3", true),
      new Content("I1", "V2", "C1", true),
      new Content("I1", "V2", "C2", true),
      new Content("I2", "V1", "C3", true)
    };

    var temp = content.GroupBy (c => new {Item = c.Item, Vendor = c.Vendor});
    var result = temp.Select (t => new 
                      {
                          Item = t.Key.Item,
                          Vendor = t.Key.Vendor,
                          C1 = content.Where(x => x.Item == t.Key.Item && x.Vendor == t.Key.Vendor && x.Compliance == "C1").Select (x => x.Status),
                          C2 = content.Where(x => x.Item == t.Key.Item && x.Vendor == t.Key.Vendor && x.Compliance == "C2").Select (x => x.Status),
                          C3 = content.Where(x => x.Item == t.Key.Item && x.Vendor == t.Key.Vendor && x.Compliance == "C3").Select (x => x.Status)
                      }).Dump();

}

// Define other methods and classes here
public class Content
{
    public Content(string item, string vendor, string compliance, bool? status)
    {
      Item = item; Vendor = vendor; Compliance = compliance; Status = status;
    }

    public string Item { get; set; }
    public string Vendor { get; set; }
    public string Compliance { get; set; }
    public bool? Status { get; set; }

}

This is the output, which should give you what you expected: