Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
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 now!
  • 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 datasetHelpful Member! 

taree (TechnicalUser) (OP)
7 Apr 10 12:58

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   
Helpful Member!  jmeckley (Programmer)
7 Apr 10 13:17
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

taree (TechnicalUser) (OP)
7 Apr 10 14:11
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
jbenson001 (Programmer)
7 Apr 10 14:11
If you are using sql server, it can be easily done in a stored procedure.
taree (TechnicalUser) (OP)
7 Apr 10 14:20
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?
jmeckley (Programmer)
7 Apr 10 14:26
var is 3.0 syntax. if you are on 2.0 then change vars to explicit types. DataRow, DataTable, List<string>, etc.

Quote:

can you do this without using dynamic query?
you could ask in the oracle forums.

Jason Meckley
Programmer
Specialty Bakers, Inc.

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

jbenson001 (Programmer)
7 Apr 10 16:53
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.
taree (TechnicalUser) (OP)
8 Apr 10 11:20
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
taree (TechnicalUser) (OP)
8 Apr 10 14:21
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
jmeckley (Programmer)
8 Apr 10 16:16
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

taree (TechnicalUser) (OP)
8 Apr 10 16:30
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
jmeckley (Programmer)
8 Apr 10 19:14
yes that should work; if not, how is it not working?

Jason Meckley
Programmer
Specialty Bakers, Inc.

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

taree (TechnicalUser) (OP)
9 Apr 10 9:02
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
jmeckley (Programmer)
9 Apr 10 9:08
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

taree (TechnicalUser) (OP)
9 Apr 10 9:37
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
taree (TechnicalUser) (OP)
9 Apr 10 10:33
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
jmeckley (Programmer)
9 Apr 10 10:34
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

jmeckley (Programmer)
9 Apr 10 11:04
your second attempt is only returning 1 row, because you only added one row to the table. you then loop through the records and assign values to the row, so you will only get the last item number.

Jason Meckley
Programmer
Specialty Bakers, Inc.

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

taree (TechnicalUser) (OP)
9 Apr 10 11:24
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
jmeckley (Programmer)
9 Apr 10 11:41
the error says it all. you are trying to compare a string to a double. try row("ITMENUM").ToString() to explicitly use strings.

Jason Meckley
Programmer
Specialty Bakers, Inc.

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

taree (TechnicalUser) (OP)
9 Apr 10 11:55
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()
 
 
jmeckley (Programmer)
9 Apr 10 11:59
syntax... in VB + is mathematical and & is for string concatenation.

Jason Meckley
Programmer
Specialty Bakers, Inc.

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

jbenson001 (Programmer)
9 Apr 10 12:04
YOu can use the"+" in VB for string contaenation, but you will have to put a .ToString after  your item:
row("ITEMNUM").toString + ....
taree (TechnicalUser) (OP)
9 Apr 10 12:06
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:
 
jmeckley (Programmer)
9 Apr 10 12:12
confirm the data type for column ITEMNUM is  string for both the source and pivot table.

Jason Meckley
Programmer
Specialty Bakers, Inc.

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

taree (TechnicalUser) (OP)
9 Apr 10 12:22
I checked both source and pivot table. for the source the datatype is char and pivot table it is string.
taree (TechnicalUser) (OP)
9 Apr 10 16:22
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
jmeckley (Programmer)
9 Apr 10 16:34
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

taree (TechnicalUser) (OP)
12 Apr 10 8:59
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
 
jmeckley (Programmer)
12 Apr 10 11:05
correct, this should be working. are you receiving errors or incorrect results from this?

Jason Meckley
Programmer
Specialty Bakers, Inc.

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

taree (TechnicalUser) (OP)
12 Apr 10 11:20
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
 
jmeckley (Programmer)
12 Apr 10 11:30
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

taree (TechnicalUser) (OP)
12 Apr 10 11:41
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!

Back To Forum

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