[uniek]

Hello everyone,

I have some problems with my program, I made join 2 tables and now i want to make a column values as header columns,

1) My tables shows something like this:

ID    Name    Produkt   Comment   Attribute

1     Mike    AA        YY        aa
1     Mike    AA        YY        cc
2     Steve   BB        YY        aa
3     None              XX        cc
4     Hube    DD               

2) I want my tables be like this:

ID    Name    Produkt   Comment   aa    cc

1     Mike    AA        YY        x     x
2     Steve   BB        YY        x
3     None              XX              x
4     Hube    DD

 
My Code for 1)

DataTable table = new DataTable("table");

            table.Columns.Add("ID", typeof(Int32));
            table.Columns.Add("Name", typeof(String)); 
            table.Columns.Add("Produkt", typeof(String));
            table.Columns.Add("Attribute", typeof(String));

            DataTable table2 = new DataTable("table2");

            table2.Columns.Add("Name", typeof(String));
            table2.Columns.Add("Comment", typeof(String));

            DataSet ds = new DataSet("DataSet");

            ds.Tables.Add(table);
            ds.Tables.Add(table2);

            object[] o1 = { 1,"Mike", "AA","aa" };
            object[] o5 = { 1, "Mike", "AA", "cc" };

            object[] o2 = { 2,"Steve","BB","aa" };
            object[] o3 = { 3,"None",null , "cc" };
            object[] o4 = { 4,"Hube", "DD",  };

            object[] c1 = { "None","XX" };
            object[] c2 = { "Steve","YY"};
            object[] c3 = { "Mike", "YY"};

            table.Rows.Add(o1);
            table.Rows.Add(o5);
            table.Rows.Add(o2);
            table.Rows.Add(o3);
            table.Rows.Add(o4);

            table2.Rows.Add(c1);
            table2.Rows.Add(c2);
            table2.Rows.Add(c3);
         
            var results = from t1 in table.AsEnumerable()
                          join tb2 in table2.AsEnumerable()
                          on t1.Field<string>("Name") equals tb2.Field<string>("Name") into prodGroup
                          from table4 in prodGroup.DefaultIfEmpty()
                          
                          select new
                          {
                              ID = t1.Field<Int32?>("ID"),
                              Name = t1.Field<String>("Name"),
                              Produkt = t1.Field<String>("Produkt"),
                              Attribute = t1.Field<String>("Attribute"),
                              Comment = table4 != null ? table4.Field<String>("Comment") : null,
                              
                          };
dataGridView2.DataSource = results.ToList();

This post has been edited by Curtis Rutland: 01 October 2012 - 07:17 AM
Reason for edit:: fixed code tags

[uniek]

I have tried with this code:

var result = new DataTable();
            result.Columns.Add("ID", typeof(Int32));
            result.Columns.Add("Name", typeof(String));
            result.Columns.Add("Produkt", typeof(String));
            result.Columns.Add("Comment", typeof(String));

            result.Columns.AddRange(
                (from c in
                     (from r in table.AsEnumerable()
                      where !r.IsNull("Attribute") && !string.IsNullOrEmpty(r.Field<string>("Attribute"))
                      select r.Field<string>("Attribute")).Distinct()
                 select new DataColumn(c, typeof(bool))).ToArray()
            );

            foreach (var r in results)
            {
                var productIndex = result.Columns.IndexOf(r.Attribute);
                var vals = new List<object>() { r.ID, r.Name, r.Produkt, r.Comment };
                for (int i = 4; i < result.Columns.Count; i++)
                {

                    if (i == productIndex)
                    {
                        vals.Add(true);

                    }
                    else
                    {
                        vals.Add(false);
                    }
                }

                result.LoadDataRow(vals.ToArray(), true);
                dataGridView2.DataSource = results.ToList();

            }

But it doesnt work like i want to. It shows a tables with Mike in 2 lines, but i want to show it just in 1 line:

ID Name Produkt Comment aa cc

1 Mike AA YY x // Here and
1 Mike AA YY x // Here should be in one line, see 1)
2 Steve BB YY x
3 None XX x
4 Hube DD

[quote name='uniek' date='01 October 2012 - 07:22 AM' timestamp='1349101375' post='1713031']
I have tried with this code:

var result = new DataTable();
            result.Columns.Add("ID", typeof(Int32));
            result.Columns.Add("Name", typeof(String));
            result.Columns.Add("Produkt", typeof(String));
            result.Columns.Add("Comment", typeof(String));

            result.Columns.AddRange(
                (from c in
                     (from r in table.AsEnumerable()
                      where !r.IsNull("Attribute") && !string.IsNullOrEmpty(r.Field<string>("Attribute"))
                      select r.Field<string>("Attribute")).Distinct()
                 select new DataColumn(c, typeof(bool))).ToArray()
            );

            foreach (var r in results)
            {
                var productIndex = result.Columns.IndexOf(r.Attribute);
                var vals = new List<object>() { r.ID, r.Name, r.Produkt, r.Comment };
                for (int i = 4; i < result.Columns.Count; i++)
                {

                    if (i == productIndex)
                    {
                        vals.Add(true);

                    }
                    else
                    {
                        vals.Add(false);
                    }
                }

                result.LoadDataRow(vals.ToArray(), true);
                dataGridView2.DataSource = results.ToList();

            }

But it doesnt work like i want to. It shows a tables with Mike in 2 lines, but i want to show it just in 1 line:

ID    Name    Produkt   Comment   aa    cc  

1     Mike    AA        YY        x          // Here and
1     Mike    AA        YY              x    // Here should be in one line, see 1)
2     Steve   BB        YY        x  
3     None              XX              x  
4     Hube    DD

But it doesnt work like i want to. It shows a tables with Mike in 2 lines, but i want to show it just in 1 line:

ID    Name    Produkt   Comment   aa    cc  

1     Mike    AA        YY        x          // Here and
1     Mike    AA        YY              x    // Here should be in one line, see 1)
2     Steve   BB        YY        x  
3     None              XX              x  
4     Hube    DD

[Skydiver]

I think that you are partway there. Here's what I would do purely on the client side without any help from the SQL server. Any SQL gurus who can make this better, please pipe up.

In pseudo code:

finalDataTable = new DataTable();
foreach(column in originalDataTable.Columns)
{
    if (column.Name != "Attribute")
        finalDataTable.Columns.Add(column.Name, columnType);
}

knownAttributes = new List<AttributeType>();
foreach(row in originalDataTable.Rows)
{
    attribute = row.Column["Attribute"].Value;
    if (!knownAttributes.Contains(row.Column["Attribute"]))
        finalDataTable.Columns.Add(attribute.ToString, bool);
}

idsToRows = new Dictionary<IdType, Row>();
foreach(oldRow in originalDataTable.Rows)
{
    id = oldRow.Column["Id"].Value;
    if (idsToRows.Contains(id))
    {
        newRow = idsToRows[id];
    }
    else
    {
        newRow = new Row();
        idsToRows.Add(id, newRow);

        foreach(column in originalDataTable.Columns)
        {
            if (column.Name != "Attribute")
                newRow.Column[column.Name] = oldRow.Column[column.Name];
        }
    }
    newRow.Column[oldRow.Column["Attribute"]] = true;
}