Results 1 to 5 of 5

Thread: Pivot Tables?

  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Unanswered: Pivot Tables?

    Hello,
    I have an ASP.NET (3.5) page using VB and a SQL Server 2005 database. The database holds historical data as follows ...
    Code:
    BATCH    |     ID     |    PLAN
    ---------------------------------
       1           1            AB
       1           2            ABC
       1           3            AB
       1           4            CD
       1           5            BC
       2           1            AB
       2           2            ABC
       2           3            AB
       2           4            AB
       2           5            BC
    Batch represents data from a certain upload, for example, data is uploaded Monday and Wednesday, Monday's records are Batch1 and Wednesday's records are Batch2. Here is my issue, I haven't done much with Pivot Tables or even #TEMP TABLE's and I'm looking for the best way to compare / report this data in the following format ...

    Code:
    BATCH     |     AB     |     ABC     |     BC     |     CD     |
    -----------------------------------------------------------------------
       1            #             #             #            #
       2            #             #             #            #
    Where # represents the number of id's with that specific plan for each batch. I've done some reading and Pivot tables seem to be what I need but I can't quite grasp it, would I need to create temp tables as well?
    Any help or guidance would be greatly appreciated.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's no such thing as pivot tables in SQL Server. In any event, do this at the front end in your presentation layer. The database maintains the integrity of the data, and serves it to consumers. The front end makes it pretty for the punters.
    .NET CrossTabs versus SQL Server CrossTabs

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh contrare!

    Using PIVOT and UNPIVOT

    But I agree that pivoting should NOT be done in the database. That is an indication of poor design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I didn't say there was no such thing as a pivot operator, only pivot tables :P

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    I used site example

    ASP / ADO Pivot Function & Example

    Works well used it in a number of SQL asp asp-net sites
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •