0

I need to make a comparison table in HTML format, the problem is that the items in database comes as a columns but in comparison table it must be a rows!

Example

The data in database looks like the following

 ID  Name     Color   Weight
-------------------------------
 1   Ball     Red     10
 2   Table    Black   50
 3   Chair    Green   30

And it must looks like the following in comparison table

ID      1         2        3
Name    Ball      Table    Chair
Color   Red       Black    Green
Weight  10        50       30

I am using ASP.NET with repeater but it didn't work, Can you please help me to find the best way to do this.

I must use something like repeater because the numbers of columns in the the comparison table varies.

Prince Waleed
  • 195
  • 8
  • You are looking for what is called a PIVOT table. I'll see if if I can find a code example for ASP. – N1tr0 Jan 25 '13 at 16:38

2 Answers2

0

A possible solution:

Given the following DataTable as datasource:

protected DataTable Data
{
    get
    {
        if (ViewState["Data"] == null)
        { 
            DataTable table = new DataTable();
            table.Columns.Add("ID", typeof(int));
            table.Columns.Add("Name");
            table.Columns.Add("Color");
            table.Columns.Add("Weight", typeof(int));

            table.Rows.Add(1, "Ball", "Red", 10);
            table.Rows.Add(2, "Table", "Black", 50);
            table.Rows.Add(3, "Chair", "Green", 30);
            ViewState["Data"] = table;
        }
        return (DataTable)ViewState["Data"];
    }
}

And some ASP code for looping and building the table:

<table>
<%
  for (int i = 0; i < Data.Columns.Count; i++)
  {
        %>
        <tr>
            <td><%= Data.Columns[i].ColumnName %></td>
            <%
                for (int j = 0; j < Data.Rows.Count; j++)
                {
                    %>
                    <td><%= Data.Rows[j][i] %></td>
                    <%
                }

             %>
        </tr>
        <%  
  } 
%>
</table>
Mateus Schneiders
  • 4,853
  • 3
  • 20
  • 40
0

So this isn't for ASP.NET but you might be able to convert it. This is code I use to create a dynamic pivot tables in SQL Server. This allows you to do what you want without having to know how many columns there are going to be. The thing about pivot tables is that they require a calculation so you will have to do that to get it to work. Hope this helps.

DECLARE @columns varchar(500)

Select @columns = STUFF(( select distinct
            '],[' + cast(Name as varchar)
              from table_name as t1
              order by '],[' + cast(Name as varchar)
              for XML path('')
              ), 1, 2, '') + ']'

DECLARE @query varchar(max)

SET @query = 'select *
             from table_name
             pivot
             (
              count(Name)
              for Name in (' +@columns+ ')
             )as pvt'

execute(@query)
N1tr0
  • 485
  • 2
  • 6
  • 24
  • Thank you for your time and answer my question, but in my case this way will be very difficult, because my queary contains a lot of columns and their in different type, and converting all of them to varchar will not allow me to deal with them easly in my calculations in page code – Prince Waleed Jan 26 '13 at 11:05