How to convert rows to columns from the dataset 1

[taree]

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

 

[jmeckley]

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

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
faq732-7259

 

[taree]

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]

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

 

[taree]

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]

var is 3.0 syntax. if you are on 2.0 then change vars to explicit types. DataRow, DataTable, List<string>, etc.

can you do this without using dynamic query?

you could ask in the oracle forums.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jbenson001]

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]

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]

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

   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]

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

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
faq732-7259

 

[taree]

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.

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]

yes that should work; if not, how is it not working?

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[taree]

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

  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]

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
faq732-7259

 

[taree]

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

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]

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.

 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]

this is a problem

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

[COLOR=red][s]
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
[/s][/color]

with

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.

For Each vendor As Object In vendors
   pivot.Columns.Add(vendor, GetType([COLOR=red][s]String[/s][/color][COLOR=blue]Int32[/color]))
Next

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jmeckley]

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
faq732-7259

 

[taree]

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

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]

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
faq732-7259