Results 1 to 15 of 15

Thread: CrossTab Query

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: CrossTab Query

    Is there a SQL version of a Crosstab Query??

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by desireemm
    Is there a SQL version of a Crosstab Query??
    Not in SQL 7.0 or SQL 2000. SQL 2005 has PIVOT which is similar (but I don't know the full syntax).

    There are also 3rd party products that you can install that will simulate a crosstab. You can search for AGS Crosstab or RAC for SQL.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking cool

    Thank you very much

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can create a static crosstab query in SQL fairly easily using CASE statements. For an excellent explanation and example, just look up CROSSTAB in books online.
    Note that this method will not produce dynamic crosstabs with variable column headers, but you really shouldn't be doing that in the first place.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    In a project I'm doing, there are crosstab queries in Access that have to be replicated in SQL via a stored procedure or else they'll take forever to run. The columns are fixed so I used a sproc to create a temp table, insert the data into it, aggregate it, then present it. It looks exactly like the old Access crosstab and executes more quickly, but it's not very flexible. Works for us though.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by disruptivehair
    ...The columns are fixed so I used a sproc to create a temp table, insert the data into it, aggregate it, then present it..
    You would probably get better efficiency by droppint the temp table method and using the CASE method recommended in Books Online.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by blindman
    You would probably get better efficiency by droppint the temp table method and using the CASE method recommended in Books Online.

    CASE statements aren't overly popular around here. We're switching to SQL Server 2005 soon so I'm anxious to get my hands on the new PIVOT functionality.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by disruptivehair
    CASE statements aren't overly popular around here. We're switching to SQL Server 2005 soon so I'm anxious to get my hands on the new PIVOT functionality.
    OK. I will bite. Why?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by disruptivehair
    CASE statements aren't overly popular around here.
    Are you writing code, or are you electing the Senior Class President?
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking Wow

    This is cool you guys are still replying to this. How are you guys doing anyways, Ive been busy creating a website

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by desireemm
    This is cool you guys are still replying to this. How are you guys doing anyways, Ive been busy creating a website
    ...and you didn't post the URL? How do you expect to pop up to the top of the search engines?
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Blindman

    Hey there long time no see how you been

    the site isnt done yet

  13. #13
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Graphics

    hey does anyone know of a good graphics software, something nice but not too hard to use

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You're asking database coders about graphics?

    This is about as far as you could go with SQL:

    http://www.chris.com/ascii/index.html
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    ok bad question

    sorry bad question, I'll ask else where

Posting Permissions

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