CODEWITHSUNDEEP
c#datatablepivot
user441521
user441521

Reputation: 6998

How to pivot on a .NET DataTable

I have data that is in format:

NodeName, ReadingDate, Value
NY, 5/10/2010, 5
NY, 5/11/2010, 7
TX, 5/10/2010, 8
TX, 5/11/2010, 9

...

I want to pivot this to look like:

ReadingDate, NY, TX
5/10/2010, 5, 8
5/11/2010, 7, 9

The kicker is there are an unknown amount of node names so I can't hardcode those. There are also about 2000 some dates and 150 node names.

Right now I make a data table where I hardcode the ReadingDate column and then use a distinct on the NodeName to get unique name values and make columns with that. Then I go in and unique on all the ReadingDates and make records with them. So now I have the columns and ReadingDates. I then go in and fill in the values. This works but as you can guess it's fairly slow. I'm wondering if there is an easier and faster way to pivot the data I have.

Here is what I have so far:

// get the data from the database
SqlDataReader rdr = cmd.ExecuteReader();

// load into a datatable for processing
DataTable tbl = new DataTable();
tbl.Load(rdr);

// add date column and a column for all NodeNames
DataTable pivotData1 = new DataTable();
pivotData1.Columns.Add("ReadingDate", typeof(DateTime));

DataView view = new DataView(tbl);
DataTable nodeNamesData = view.ToTable(true, "NodeName");
foreach (DataRow row in nodeNamesData.Rows)
{
    string data = row["NodeName"].ToString();
    DataColumn col = new DataColumn(data, typeof(double));
    col.DefaultValue = 0;
    pivotData1.Columns.Add(col);
}

// add all unique row labels
DataView dateView = new DataView(tbl);
DataTable dateData = dateView.ToTable(true, "ReadingDate");
foreach (DataRow row in dateData.Rows)
{
    DataRow newRow = pivotData1.NewRow();
    newRow["ReadingDate"] = row["ReadingDate"];

    pivotData1.Rows.Add(newRow);
}

// fill in the rest of the row data
foreach (DataRow row in pivotData1.Rows)
{
    var rowLabel = DateTime.Parse(row["ReadingDate"].ToString());

    // query to get all nodes for this date
    var qry = (from a in tbl.AsEnumerable()
               where a.Field<DateTime>("ReadingDate") == rowLabel
               select a);

    // loop over and fill in the column data for this date
    foreach (var r in qry)
    {
        double val = 0.0;

        if(r["Value"].ToString() != string.Empty)
            val = Convert.ToDouble(r["Value"].ToString());

        string col = r.Field<string>("NodeName");

        Console.WriteLine("Date = " + rowLabel + " Node = " + col + " Value = " + val);

        row.SetField<double>(col, val);
    }
}

Upvotes: 0

Views: 2501

Answers (1)

Heinzi
Heinzi

Reputation: 172280

Step 1: Get a list of all the node names (SELECT DISTINCT NodeName FROM myTable).

Step 2: Put them in brackets and create a single, comma-separated string: [NY], [TX], ....

Step 3: Use this string to construct an SQL Server PIVOT statement which returns the desired data set.

Note: Since the node names will be part of your SQL statement, be sure to sanitize them to avoid SQL injection. (Unfortunately, using SQL parameters is not an option here, since the node names effectively become column names.)

Upvotes: 1

Related Questions