Transpose in Datatable in ASP.NET

Question

I have DataTable with two Column as "ID", "Value" with data as:

ID    Value
A   100
A   200
A   300
A   400
A   500
B   -100
B   -99
B   -98
B   -97
C   1
C   2
C   3
C   4

I want to display this in GridView as:

Which is the best way to Transpose this in DataTable. It would be really helpful if any one can provide an example.

Regards

Are you getting it from your database like that? If you are you could use this http://msdn.microsoft.com/en-us/library/ms177410.aspx — Greg, Mar 31 '12 at 19:10
Yes I am getting this from DataBase which I can not change. Its non relational database with three column "TAG", "Value" and "Time". Its a product of OSI and known as "PI" database. — iPhoneDev, Mar 31 '12 at 19:24

richardtallent · Answer 1 · 2012-03-31T21:56:04.060

1

I'm fairly familiar with OSI PI data and I've done the same after pulling PI tag data into an SQL Server database.

The trick is that there needs to be another column with the Start Time or End Time so the correct rows for A, B, and C can be matched up.

Then it's just a matter of using PIVOT (SQL Server 2005+ only) to group them:

SELECT *
FROM (SELECT ts_start, ID, Value FROM DataTable) v
PIVOT( SUM(Value) FOR ID IN ([A],[B],[C]) ) AS pvt

You can use just about any aggregate you want (MAX, MIN, SUM, etc.) above, it doesn't matter as long as there's only one value for each distinct combination of tag and timestamp. Aggregation is required by PIVOT, as are the aliases v and pvt (you can name them whatever you like).

edited Mar 31 '12 at 21:56

answered Mar 31 '12 at 21:50

richardtallent

34,724
14
83
123

thanks, but I can I use this in C# as I am not using linked server(SQL Server). I am directly fetching PI Archive vale using OLED. Now I want to display above result on GridView. – iPhoneDev Mar 31 '12 at 22:15
Ah, I missed that you're calling this directly via OLEDB. In that case, I think you should look into using LINQ to pivot the data in a similar way, something like this answer: http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq You can also adjust your PI SQL to use LEFT JOIN to join each of the tables for A, B, and C together on the same ts_start/ts_end value. That will give you a single result set with columns for each tag value and their common timestamp. – richardtallent Apr 01 '12 at 07:00

Paul Aldred-Bann · Answer 2 · 2012-07-17T10:23:27.583

Try this function (something i've recently had to do myself). The params are:

dtTableToTranspose = the table you want to transpose (obviously)

index = the column index that has the row key (in your case it will be 0)

private static DataTable TransposeADONETDataTable(DataTable dtTableToTranspose, Int32 index)
{
    DataTable dtTransposedTable = new DataTable("TransposedTable");

    String colName = dtTableToTranspose.Columns[index].ColumnName.ToString();
    dtTransposedTable.Columns.Add(colName);

    foreach (DataRow row in dtTableToTranspose.Rows)
    {
        dtTransposedTable.Columns.Add(row[index].ToString());
    }

    Int32 colIndex = 0;

    foreach (DataColumn dc in dtTableToTranspose.Columns)
    {
        if (colIndex != index)
        {
            DataRow newRow = dtTransposedTable.NewRow();
            newRow[0] = dc.ColumnName;

            for (Int32 destColIndex = 1; destColIndex < dtTransposedTable.Columns.Count; destColIndex++)
            {
                newRow[destColIndex] = dtTableToTranspose.Rows[destColIndex - 1][colIndex];
            }

            dtTransposedTable.Rows.Add(newRow);
        }

        colIndex++;
    }

    return dtTransposedTable;
}

Transpose in Datatable in ASP.NET

2 Answers2

Linked