INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

How to convert rows to columns from the dataset

Share

How to convert rows to columns from the dataset

How to convert rows to columns from the dataset

(OP)

CODE

I have the below information in my dataset and I am just wondering how i can convert the row
to column show below as a desired output. please help and as always your help is appreciated

ITMENUM          VENDORNAME       VENDORSPRICE

2103.501/00010    L240                15000
2103.501/00010    M0154              8900
2103.501/00010    N0063                11500



ITMENUM            L240    M0154   N0063                          

2103.501/00010    15000     8900    11500   

RE: How to convert rows to columns from the dataset

looks like a pivot table.
1. loop through the datatable and select distinct vendor names. this list will be used to create the new data table.
2. create the new datatable. add a column for the item number and another column for each vendor in the distinct list of vendors from step 1.
3. loop through the original table again and populate the appropriate columns
something like

CODE

public DataTable PivotItemsByVendor(DataTable source)
{
   var vendors = new List<string>();
   foreach(var row in source.Rows)
   {
       var vendor = row["VENDORNAME"].ToString();
       if(vendors.Contains(vendor)) continue;
       vendors.Add(vendor);
   }

   var pivot = new DataTable();
   pivot.Columns.Add("ITMENUM");
   foreach(var vendor in vendors)
   {
      pivot.Columns.Add(vendor, typeof(int));
   }

   foreach(var row in source.Rows)
   {
       var rows = pivot.Select("[ITEMNUM]="+row["ITEMNUM"]);

       var pivotRow = pivot.NewRow();
       if (rows.Length == 1)
       {
           pivotRow = rows[0];
       }
       pivotRow["ITEMNUM"] = row["ITEMNUM"];
       pivotRow[row["VENDORNAME"]] = row["VENDORSPRICE"];
   }

   return pivot;
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

FAQ855-7190: Database Connection Management
FAQ732-7259: Keeping the UI responsive

RE: How to convert rows to columns from the dataset

(OP)
Thank you Jason for the help. I am trying to understand your code and I am geting this error message:

Dim vendors As Var = New List(Of String)()

var is not defined

thank you

RE: How to convert rows to columns from the dataset

If you are using sql server, it can be easily done in a stored procedure.

RE: How to convert rows to columns from the dataset

(OP)
jbenson001 I am using oracle version 10 and I am not sure if how this can be done without going through a lot of hassel, like using dynamic query. can you do this without using dynamic query?

RE: How to convert rows to columns from the dataset

Yes, as Jason says, post in the Oracle forums.  In SQL Server they have PIVOT and UNPIVIOT functions.  Oracle is more established and I would bet there is something equal to that in Oracle.

RE: How to convert rows to columns from the dataset

(OP)
Thank you so much for the help I get here. I really appreciate your willingness to help out other like me. I am working with the Jasons sample code and it looks like that will work for what I need. I will report back if I encounter any issue. thank  you all

RE: How to convert rows to columns from the dataset

(OP)
I am not clear with this line: can you explain it to me? thanks
  
what is the purpose of this line. the rest of the line is very clear excetp this one
Dim rows As Object = pivot.[Select]("[ITMENUM]=" + row("ITMENUM"))

            Dim pivotRow As DataRow = pivot.NewRow()
            If rows.Length = 1 Then
                pivotRow = rows(0)
            End If


CODE

   Dim vendors As Object = New List(Of String)()
        For Each row As DataRow In source.Rows
            Dim vendor As String = row("VENDORNAME").ToString
            If vendors.Contains(vendor) Then
                Continue For
            End If
            vendors.Add(vendor)
        Next

        Dim pivot As  DataTable = New DataTable()
        pivot.Columns.Add("ITMENUM")
        For Each vendor As Object In vendors
            pivot.Columns.Add(vendor, GetType(String))
        Next




*****the code below does return a  blank pabe**************


        For Each row As DataRow In source.Rows

            Dim rows As Object = pivot.[Select]("[ITMENUM]=" + row("ITMENUM"))

            Dim pivotRow As DataRow = pivot.NewRow()
            If rows.Length = 1 Then
                pivotRow = rows(0)
            End If
            Dim test As String = row("ITMENUM")
            pivotRow("ITMENUM") = row("ITMENUM")
            pivotRow(row("VENDORNAME")) = row("VENDORSPRICE")
        Next

        Return pivot

RE: How to convert rows to columns from the dataset

select is checking if the row exists or not. if it doesn't exist we create a new row, otherwise we use the existing row.
I did realize there is a problem with the code above. if a new row is created, it needs to be added to the table. something like

CODE

var pivotRow = pivot.Select(...);
if(pivotRow == null)
{
   pivotRow = pivot.NewRow();
   pivot.Rows.Add(pivotRow);
}
pivotRow["ITEMNUM"] = row["ITEMNUM"];
pivotRow[row["VENDORNAME"]] = row["VENDORSPRICE"];

Jason Meckley
Programmer
Specialty Bakers, Inc.

FAQ855-7190: Database Connection Management
FAQ732-7259: Keeping the UI responsive

RE: How to convert rows to columns from the dataset

(OP)
I thank you for your help Jason. I am still confused with the below line of code.I added your code from above and I am still not getting the righ result. let say I have

itemnum: 2103.501/00010 three times in the dataset for the three vendors but I want this itemnum to be displayed only once.how can I make sure that this is happening. every thing is clear excetp the below line of code. I thank you for the help and patient as I try to understand this.

CODE

For Each row As DataRow In source.Rows

            Dim rows As Object = pivot.[Select]("[ITMENUM]=" + row("ITMENUM"))

            ''********This creates row(s)
            Dim pivotRow As DataRow = pivot.NewRow()

            If rows.Length = 1 Then
                pivotRow = rows(0)
            End If

            If pivotRow Is Nothing Then
                pivotRow = pivot.NewRow()
                pivot.Rows.Add(pivotRow)
            End If

            pivotRow("ITMENUM") = row("ITMENUM")
            pivotRow(row("VENDORNAME")) = row("VENDORSPRICE")

        Next

RE: How to convert rows to columns from the dataset

(OP)
it only created the column name and the dataset is blank. I am not sure why it is blank.

CODE

  For Each row As DataRow In source.Rows

            Dim rows As Object = pivot.[Select]("[ITMENUM]=" + row("ITMENUM"))

            ''********This creates row(s)
            Dim pivotRow As DataRow = pivot.NewRow()


            If pivotRow Is Nothing Then
                pivotRow = pivot.NewRow()
                pivot.Rows.Add(pivotRow)
            End If

            If rows.Length = 1 Then
                pivotRow = pivotRow(0)
            End If

            pivotRow("ITMENUM") = row("ITMENUM")
            pivotRow(row("VENDORNAME")) = row("VENDORSPRICE")


        Next

        Return pivot

RE: How to convert rows to columns from the dataset

step through the code to ensure the records are transferred to the new data table. output the pivot table to the console or immediate window to ensure the records are properly added. if they are not then we can adjust how new rows are added/updated.

Jason Meckley
Programmer
Specialty Bakers, Inc.

FAQ855-7190: Database Connection Management
FAQ732-7259: Keeping the UI responsive

RE: How to convert rows to columns from the dataset

(OP)
Thank you Jason for being patient with me. I step through the code and the new data table is empty. The only thing that I see is the columns are created and the rows are not there. thank you

CODE


Public Function PivotItemsByVendor(ByVal source As DataTable) As DataTable


        Dim vendors As Object = New List(Of String)()

        For Each row As DataRow In source.Rows
            Dim vendor As String = row("VENDORNAME").ToString
            If vendors.Contains(vendor) Then
                Continue For
            End If
            vendors.Add(vendor)
        Next

        Dim pivot As DataTable = New DataTable()

        pivot.Columns.Add("ITMENUM")
        For Each vendor As Object In vendors
            pivot.Columns.Add(vendor, GetType(String))
        Next


        For Each row As DataRow In source.Rows
            Dim rows As Object = pivot.[Select]("[ITMENUM]=" + row("ITMENUM"))
            Dim pivotRow As DataRow = pivot.NewRow()

            If pivotRow Is Nothing Then
                pivotRow = pivot.NewRow()
                pivot.Rows.Add(pivotRow)
            End If

            If rows.Length = 1 Then
                pivotRow = pivotRow(0)
            End If

            pivotRow("ITMENUM") = row("ITMENUM")
            pivotRow(row("VENDORNAME")) = row("VENDORSPRICE")


        Next

        Return pivot

    End Function

RE: How to convert rows to columns from the dataset

(OP)
Jason, After I did some twick to your code it gives me one row. I am supposed to get three row thought. Is there anything I am missing here.

CODE

 Public Function PivotItemsByVendor(ByVal source As DataTable) As DataTable

        Dim vendors As Object = New List(Of String)()

        For Each row As DataRow In source.Rows
            Dim vendor As String = row("VENDORNAME").ToString
            If vendors.Contains(vendor) Then
                Continue For
            End If
            vendors.Add(vendor)
        Next

        Dim pivot As DataTable = New DataTable()

        pivot.Columns.Add("ITMENUM")
        For Each vendor As Object In vendors
            pivot.Columns.Add(vendor, GetType(String))
        Next
        Dim pivotRow As DataRow = pivot.NewRow()

        If pivotRow IsNot Nothing Then
            pivotRow = pivot.NewRow()
            pivot.Rows.Add(pivotRow)
        End If

        For Each row As DataRow In source.Rows

            pivotRow("ITMENUM") = row("ITMENUM")
            pivotRow(row("VENDORNAME")) = row("VENDORSPRICE")

        Next
        Return pivot

    End Function

RE: How to convert rows to columns from the dataset

this is a problem

CODE

Dim pivotRow As DataRow = pivot.NewRow()
If pivotRow Is Nothing Then
   pivotRow = pivot.NewRow()
   pivot.Rows.Add(pivotRow)
End If
pivotRow will never be nothing, because it's always a new row.
replace

CODE


Dim rows As Object = pivot.[Select]("[ITMENUM]=" + row("ITMENUM"))
Dim pivotRow As DataRow = pivot.NewRow()

If pivotRow Is Nothing Then
    pivotRow = pivot.NewRow()
    pivot.Rows.Add(pivotRow)
End If

If rows.Length = 1 Then
    pivotRow = pivotRow(0)
End If
with

CODE

Dim rows As Object = pivot.[Select]("[ITMENUM]=" + row("ITMENUM"))
Dim pivotRow As DataRow = pivot.NewRow()

If rows.Length = 1 Then
    pivotRow = pivotRow(0)
Else
    pivot.Rows.Add(pivotRow);
End If
i also noticed that you set up vendor columns as string instead of int. I would think you would want it to be numbers, not strings.

CODE

For Each vendor As Object In vendors
   pivot.Columns.Add(vendor, GetType(StringInt32))
Next

Jason Meckley
Programmer
Specialty Bakers, Inc.

FAQ855-7190: Database Connection Management
FAQ732-7259: Keeping the UI responsive

RE: How to convert rows to columns from the dataset

(OP)
Jason, hope this is the last question :) I am getting the below error message now. the itmenum data type is string and I am not sure why i get this error message

CODE

Cannot perform '=' operation on System.String and System.Double.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.EvaluateException: Cannot perform '=' operation on System.String and System.Double.

Source Error:


Line 361:        For Each row As DataRow In source.Rows
Line 362:
Line 363:            Dim rows As Object = pivot.[Select]("[ITMENUM]=" + row("ITMENUM"))
Line 364:            Dim pivotRow As DataRow = pivot.NewRow()
Line 365:
 

Source File: S:\bidAnalysis.aspx.vb    Line: 363

RE: How to convert rows to columns from the dataset

(OP)
I tried that before I post here.I am still getting the same error message;

CODE

Line 363:            ' Dim rows As Object = pivot.[Select]("[ITMENUM]=" + row("ITMENUM").ToString())
Line 364:
Line 365:            Dim rows As Object = (pivot.[Select]("[ITMENUM]=" + row("ITMENUM"))).ToString()
Line 366:
Line 367:            Dim pivotRow As DataRow = pivot.NewRow()
 
 

RE: How to convert rows to columns from the dataset

YOu can use the"+" in VB for string contaenation, but you will have to put a .ToString after  your item:
row("ITEMNUM").toString + ....

RE: How to convert rows to columns from the dataset

(OP)
so far no luck for me :) same error

CODE

Cannot perform '=' operation on System.String and System.Double.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.EvaluateException: Cannot perform '=' operation on System.String and System.Double.

Source Error:


Line 361:        For Each row As DataRow In source.Rows
Line 362:
Line 363:            Dim rows As Object = pivot.[Select]("[ITMENUM]=" & row("ITMENUM").ToString())
Line 364:            Dim pivotRow As DataRow = pivot.NewRow()
Line 365:
 

RE: How to convert rows to columns from the dataset

(OP)
I checked both source and pivot table. for the source the datatype is char and pivot table it is string.

RE: How to convert rows to columns from the dataset

(OP)
I fixed the above error message. Itmenum column has "/"
like this: 2021.501/00010. I removed both "." and "/" and the error disapeared. Now I am getting a different error

CODE

Unable to cast object of type 'System.DBNull' to type 'System.Data.DataRow'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.Data.DataRow'.

Source Error:


Line 367:
Line 368:            If rows.Length = 1 Then
Line 369:                pivotRow = pivotRow(0)
Line 370:                '  pivot.Rows.Add(pivotRow)
Line 371:            Else
 

Source File: S:\bidAnalysis.aspx.vb    Line: 369

RE: How to convert rows to columns from the dataset

taree, it looks like you are dependent on us fixing every error you encounter related to pivoting. the stack trace gives you everything you need to solve the error. if you understand what the code is doing it makes perfect sense why you are getting this error.

Jason Meckley
Programmer
Specialty Bakers, Inc.

FAQ855-7190: Database Connection Management
FAQ732-7259: Keeping the UI responsive

RE: How to convert rows to columns from the dataset

(OP)
Jason, it is my understanding that the array of DataRow objects returned by the Select() method, do contain actual data.They are the actual DataRow objects from the Rows collection that met the filter string.

rows.Length = 1 when there is an existing rows. Please correct me if I am wrong.

CODE

   For Each row As DataRow In source.Rows

            Dim rows As Object = pivot.[Select]("[ITMENUM]=" & (row("ITMENUM").ToString))
            Dim test As String = row("ITMENUM").ToString
            Dim pivotRow As DataRow = pivot.NewRow()

            If rows.Length = 1 Then
                pivotRow = pivotRow(0)

            Else
                pivot.Rows.Add(pivotRow)
            End If

            pivotRow("ITMENUM") = row("ITMENUM")
            pivotRow(row("VENDORNAME")) = row("VENDORSPRICE")

        Next
 

RE: How to convert rows to columns from the dataset

(OP)
I am still strugling to fix the error message. I just can not see why this is not working. the first time it is going the for loop with out any issue but the second time it gives me this error.how can I validate that the

CODE

Unable to cast object of type 'System.DBNull' to type 'System.Data.DataRow'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.Data.DataRow'.

Source Error:


Line 368:
Line 369:            If rows.Length = 1 Then
Line 370:                pivotRow = pivotRow(0)
Line 371:
Line 372:            Else
 

RE: How to convert rows to columns from the dataset

pivotRow is referring to a row. pivotRow(0) refers to the 1st column of that row. what you want is to set the pivotrRow to the first row found in the collection of rows

CODE

If rows.Length = 1 Then
   pivotRow = rows(0)
Else
   pivot.Rows.Add(pivotRow)
End If

Jason Meckley
Programmer
Specialty Bakers, Inc.

FAQ855-7190: Database Connection Management
FAQ732-7259: Keeping the UI responsive

RE: How to convert rows to columns from the dataset

(OP)
That did it. I just can not thank you enough for your patient and willingness to help me here. I really appreciate your help and time. You are an awesome guy !!!!!

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close