Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004

    Unanswered: crosstab stored procedure

    I have a table that has the following information like this:

    Name Date 1st item 2nd item 3rd item 4th item 5th item
    Defaul name 11/1/2005 100 0 0 0 0
    Defaul name 11/2/2005 100 0 0 0 0
    Defaul name 11/3/2005 99.69 0 0 0 0

    I need to create a crosstab query (NOT using crystal report) that will display the information like:

    11/1/2005 11/2/2005 11/3/2005
    1st item 100.00 100.00 99.69
    2nd item 0.00 0.00 0.00
    3rd item 0.00 0.00 0.00
    4th item 0.00 0.00 0.00
    5th item 0.00 0.00 0.00

    I have never used a crosstab query in sql server before. Please help!

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    First, look up CROSSTAB in Books Online and you will see the general method for handling this using CASE statements.
    Unfortunately you are going to have problems if your column headers are dynamic, which is often the case when you are grouping your columns by date values.
    If, as your example implies, you are only dealing with one year's worth of data at a time, then you can group your columns by datepart(month...), which will give you 12 consistent column labels.
    Avoid dynamic crosstab queries if at all possible (or until you upgrade to SQL Server 2005...). And though you apparently already know this, I have to say that dynamic crosstab functionality does not really belong in SQL server anyway, SQL Server 2005 not withstanding. It is a presentation issue.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Feb 2004


    Thanks for your reply. Unfortunately, the column values have to be dynamic. There is an easy way to present this in crystal reports but unfortunately i have to write directly to an excel file from a stored procedure. I'm at a loss right now as to how to accomplish this presentation in a stored procedure with dynamic columns.

  4. #4
    Join Date
    Dec 2002
    Not to beat a dead horse, but look at ags crosstab and/or RAC (used to be replacement for access crosstab, but they changed their name).


    Have you hugged your backup today?

Posting Permissions

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