Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: help on query pivot...

    hi,
    I've table TAB_QUESTION:

    ID_QUESTION..........VALUE_ID..........NUM_ANSWER. .....DESC_ANSWER
    XB1.................1...................0......... .....YES
    XB1.................2...................0......... .....NO
    XB1.................3...................1......... .....GOOD
    XB1.................4...................0......... .....SUFF
    XB1.................5...................1......... .....NO_GOOD
    XB1.................6...................0......... .....NR
    XB1.................7...................0......... .....NN

    YB1.................1...................1......... .....YES
    YB1.................2...................2......... .....NO
    YB1.................3...................3......... .....GOOD
    YB1.................4...................0......... .....SUFF
    YB1.................5...................3......... .....NO_GOOD
    YB1.................6...................2......... .....NR
    YB1.................7...................1......... .....NN

    ZC1.................1...................0......... .....YES
    ZC1.................2...................0......... .....NO
    ZC1.................3...................0......... .....GOOD
    ZC1.................4...................0......... .....SUFF
    ZC1.................5...................0......... .....NO_GOOD
    ZC1.................6...................0......... .....NR
    ZC1.................7...................1......... .....NN

    TC1.................1...................1......... .....YES
    TC1.................2...................1......... .....NO
    TC1.................3...................1......... .....GOOD
    TC1.................4...................1......... .....SUFF
    TC1.................5...................1......... .....NO_GOOD
    TC1.................6...................0......... .....NR
    TC1.................7...................0......... .....NN

    .................................................. ........
    .................................................. ........

    I've always JUST 7 (seven) DESC_ANSWER (YES,NO,GOOD,SUFF,NO_GOOD,NR,NN)
    Now I'd like to have ID_QUESTION like columns and DESC_ANSWER like rows.

    like this:

    DESC_ANSWER..........XB1........YB1............ZC1 ........TC1
    YES.........................0..........1.......... ....0...........1
    NO...........................0..........2......... .....0...........1
    GOOD.......................1..........3........... ...0...........1
    SUFF........................0..........0.......... ....0...........1
    NO_GOOD..................1..........3............. .0...........1
    NR...........................0..........2......... .....0...........0
    NN...........................0..........1......... .....1...........0

    How Can I write this query to get this output??

    Thanks in advance!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    go to Books Online and read the article entitled Cross-Tab Reports and then read about the CASE statement.
    “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.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just an observation if you want to save yourself LOTS of aggrivation... Cross tabulations (aka pivot tables) are a presentation issue, not a data management issue. As presentation issues, they are much easier to handle on the client side than the server side, meaning that your existing presentation software (be it a web page or an MS-Access ADP file) is usually better able to handle them than SQL Server can.

    I usually approach these tasks as a hybrid, where I have SQL Server do the aggrigation (in your case the sum by DESC_ANSWER and ID_QUESTION), then I have a pivot grid control actually manage the presentation. This gets me the best of both worlds, where SQL is doing the "heavy lifting" of data, and the client can do the "elegant presentation" of the results.

    -PatP

Posting Permissions

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