none
TRANSFORM, PIVOT and GridView RRS feed

  • Question

  • Hello,

     

    I have a pivoted Table where the phone numbers are listed based on calling order for a particular person.  This is an example of how a few records in the table would look like:

     

     

    Name             1st Phone          2nd Phone          3rd Phone

    John Doe        234-555-8765     234-555-2222      223-999-2211

    Kelly O'Brian   432-555-1836     432-777-4444      777-555-1234

    Steve Smith    876-555-8453     867-555-0823

     

    When I load the data into a Gridview that has manually created Columns and call all of the data in the database, I know it works because I have enough columns for all potential users in the database.  However, when I want to filter by a particular user, I don't know off-hand how many phone numbers a particular user might have and I get a runtime error if a particular user has too few numbers for the manually created columns in the Gridview. 

     

    Any suggestions on how I could dynamically add a column based on the results of the TRANSFORM/PIVOT?

     

     

     

     

    Friday, March 7, 2008 9:07 PM

Answers

  •  

    You will likely need to manually reorder to manually create the columns in the GridView. If you move the columns in the GridView column collection, it should be reflected in the display. I am not a UI expert, but this work. You can also reorder the DataColumns, either in the SQL (if possible given your pivot) or in code.

     

    Thanks,

    Erick

    Tuesday, March 11, 2008 9:45 PM
    Moderator

All replies

  • Moveing to the ADO.NET Data Access forum.
    Monday, March 10, 2008 12:30 PM
  • Are you performing the pivot in SQL Server? Are the results of the pivot loaded into a DataSet? If so, you should be able to execute the pivot from within the DataSet Designer in order to load the data.

     

    Thanks,

    Erick

     

     

     

    Tuesday, March 11, 2008 3:29 AM
    Moderator
  • The Pivot is being performed in the SQL code that is in my VB application.  The results are being sent to a Dataset.

     

    The Pivot works, but the problem for me is if I want to execute that PIVOT on an individual user using a GridView with Manually created rows.  Let's say I have three columns manually created for phone numbers in the Pivot.  Well, if the individual record only has two phone numbers, I get a runtime error saying that it can't find that third phone number column.  If I Autogenerate the columns, everything works, but the order of the columns of the GridView isn't how I want. 

     

    I'm seemingly stuck between a rock and a hard place...

    Tuesday, March 11, 2008 7:29 PM
  •  

    You will likely need to manually reorder to manually create the columns in the GridView. If you move the columns in the GridView column collection, it should be reflected in the display. I am not a UI expert, but this work. You can also reorder the DataColumns, either in the SQL (if possible given your pivot) or in code.

     

    Thanks,

    Erick

    Tuesday, March 11, 2008 9:45 PM
    Moderator