The attached file shows a simple example of the problem I'm trying to resolve.
(My actual problem is one where the number of rows and columns is closer to hundreds or thousands of rows / columns, so I could do with a solution which is not just specific to this case)
I start with a table (Format A) where:
- the names of products (cars in the example) are the column titles;
- the names of attributes (colour, age, engine size etc) are the row titles;
- the values of those attributes are in the main body of the table and may be null.
To save this in SQL Server, I transform the data to a simple table of three columns (Format B):
The two problems I need to solve are:
(1) How can I display the SQL data back in the original format (Format A)? (both cars and attribute names can be in any order)
(2) I need to group the data according to common combinations of attributes. e.g.
select colour, engine, age FROM theTable group by colour, engine, age
BUT!!!! in both cases, the solution needs to be dynamic, so the views/SPs have to determine the list of attributes to group on (and list of cars to select from) at runtime.
My actual case will expand by number of attributes and number of procucts (cars) fairly regularly, so hard coding attribute names or car names into the SP or query is not feasible.
(PS - this topic shouldn't be listed under asp.net but for some reason in IE9, I can't deselect this!)