Asp.net pivot table

Question

My customer has two table in his eCommerce DB

No(PK), PropertyName

and

No(PK), ProductNo(FK), PropertyNo(FK), Value

He just want me to make a table like that

+-----------+-------------+--------------+------------+
|           | Property 1  | Property 2   | .. all properties-> | 
+-----------+-------------+--------------+------------+
| Product1  |      x      |     4        |      x     |
| Product2  |      2      |     x        |      1     |
| Product3  |      x      |     x        |      x     |
|   ...     |             |              |            |
| (all products)          |              |            |
+----+-------------+---------------------+------------+

I tried to make it via repeater but i couldn't. How can I achieve it?

I give up and made the solution as @Bala R But a little changes...

The sample classes

public class list {
        public int No { get; set; }
        public string PropertyName { get; set; }
    }

    public class list2 {
        public int ProductNo { get; set; }
        public int PropertyNo { get; set; }
        public int Value { get; set; }

    }
    public class list3 {
        public string ProductName { get; set; }
        public int No { get; set; }

    }

The sample lists,

        List<list> propertyList = new List<list>();
        List<list2> propertyProductList = new List<list2>();
        List<list3> productList = new List<list3>();
        propertyList.Add(new list { No = 1, PropertyName=  "Property 1" });
        propertyList.Add(new list { No = 2, PropertyName = "Property 2" });
        propertyList.Add(new list { No = 3, PropertyName = "Property 3" });
        propertyList.Add(new list { No = 4, PropertyName = "Property 4" });

        propertyProductList.Add(new list2 { ProductNo = 1,  PropertyNo = 1, Value = 3 });
        propertyProductList.Add(new list2 { ProductNo = 2, PropertyNo = 3, Value = 13 });
        propertyProductList.Add(new list2 { ProductNo = 2, PropertyNo = 2, Value = 8 });
        propertyProductList.Add(new list2 { ProductNo = 3, PropertyNo = 2, Value = 6 });
        propertyProductList.Add(new list2 { ProductNo = 4, PropertyNo = 1, Value = 2 });
        propertyProductList.Add(new list2 { ProductNo = 3, PropertyNo = 1, Value = 55 });


        productList.Add(new list3 { No = 1, ProductName = "Ball" });
        productList.Add(new list3 { No = 2, ProductName = "Book" });
        productList.Add(new list3 { No = 3, ProductName = "Pencil" });
        productList.Add(new list3 { No = 4, ProductName = "TV" });

and the solution,

        var resultSet = (from c in list2
                         group c by c.ProductNo into g
                          select new {
                              ProductNo = g.Key,
                              Value = g
                          }).ToList();
        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn("Products"));
        foreach (var item in list) {
            dt.Columns.Add(new DataColumn() { ColumnName = item.PropertyName });
        }
        foreach (var item in resultSet) {
            DataRow dr = dt.NewRow();
            dr["Products"] = list3.First(p=> p.No== item.ProductNo).ProductName;
            foreach (var item2 in item.Value) {
                dr[list.First(l=>l.No == item2.PropertyNo).PropertyName] = item2.Value;
            }
            dt.Rows.Add(dr);
        }
        dataGrid1.DataSource = dt;
        dataGrid1.DataBind();

Ditch the server controls and build it with LINQ to SQL and a string builder. — The Muffin Man, Mar 24 '11 at 22:28
And your code behind? How are you associating the data source to the repeater? — WorldIsRound, Mar 24 '11 at 22:32
Do you want to pivot the data? This can be done in sql: http://stackoverflow.com/questions/2987078/how-to-query-many-to-many-table-one-tables-values-becomes-column-headers — CRice, Mar 24 '11 at 23:07
@CRice i think the post really helpfull for me thanks. And thanks all replies. — tuxm4n, Mar 24 '11 at 23:20

score 1 · Answer 1 · answered Mar 24 '11 at 23:06

You can create classes like this

    class Product
    {
        public int ProductNo { get; set; }
        public string ProductName { get; set; }
    }

    class Property
    {
        public int PropertyNo { get; set; }
        public string PropertyName { get; set; }
    }

    class Value
    {
        public int ProductPropertyNo { get; set; }
        public int ProductNo { get; set; }
        public int PropertyNo { get; set; }
        public string Value { get; set; }
    }

and load enumerations

        IEnumerable<Product> products = GetProducts();
        IEnumerable<Property> properties = GetProperties();
        IEnumerable<Value> values = GetValues();

and do something like this for DataTable

        DataTable dt = new DataTable();

        dt.Columns.Add(new DataColumn("ProductName"));

        foreach (var propNo in values.Select(v => v.PropertyNo).Distinct())
        {
            dt.Columns.Add(
                new DataColumn(properties.Where(p => p.PropertyNo == propNo).First().PropertyName));


        }

        foreach (var prodNo in  values.Select(v => v.ProductNo).Distinct())
        {
            Product  prod = products.Where(p => p.ProductNo == prodNo).First();

            DataRow dr = dt.NewRow();
            dr["ProductName"] = prod.ProductName;
            foreach (var value in values.Where(v => v.ProductNo == prodNo))
            {
                Property prop = properties.Where(p => p.PropertyNo == value.PropertyNo).First();
                dr[prop.PropertyName] = value.Value;
            }
        }

Thank you. I took the codes into my notes ;) i tried http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq if i wont achieve, i'll use it, promise ;) — tuxm4n, Mar 24 '11 at 23:21

score 0 · Answer 2 · answered Mar 24 '11 at 22:46

0

I'm not a big fan of DataTable but in this case it's the easiest way to achieve that. Create first a Column "Product" and then for each property create a new Column on the DataTable. Add DataRows for all your Products and fill out the Value in the appropriate Property Column.

Afterwards bind the DataTable to a DataGrid.

answered Mar 24 '11 at 22:46

gsharp

26,016
21
83
126

Thanks for your answer.. actually i'm not a big fan of DataTable too but yes, it seems the easiest way to do. But is there anyway to do with repeater and more code inline.. I do not want to use neither datatable nor any server side controls event like ItemDataBound. – tuxm4n Mar 24 '11 at 22:58
You don't need to use server side events. just dataGrid.DataSource = yourTable; dataGrid.DataBind(); and it's done. – gsharp Mar 24 '11 at 23:05
wow! I started writing some code example and got distracted with something else and I came back, finished it and posted it only to see this answer. It's the same idea but I guess the code helps a little bit more. – Bala R Mar 24 '11 at 23:09
i'll give +1 to when i'll be 15 :) – tuxm4n Mar 24 '11 at 23:23

Asp.net pivot table

2 Answers2