Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2008
    Posts
    11

    Unanswered: simple Crosstab query

    Hi ,

    I have a table in Sql Server 2000 where there are two columns as Name and value .It also has a primary key variableID . Now , i want to show the rows in colmn 'name' as columns and their respective values below it .

    eg,

    My table is like this .

    VariableID Name Value
    1 ABC 1155
    2 XYZ 1245
    3 PQR 1015

    i want the result like

    Name ABC XYZ PQR
    Value 1155 1245 1015

    I cant use the dyanamic crosstab procedure which i found on many forums while google search.I want a simple query.

    Can anybody help me pls.

    Thanks in advance.

    Regards
    - AVD

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    two questions --

    1. do you know in advance how many names there are?

    2. why can't you do the pivotting in your application layer with arrays?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Posts
    11
    1) Yes, i know in advance how many names are there. The table is for the Banking heads (posts) names and their code. These are fixed number. But it can increase if a new head is created.

    2)This is the demand of the developers to show data by using query , i dont work in frontend so i dont understand application layer.

    Thanks
    - AVD

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    which version of sql server?
    “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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tail (front-end developers) wagging dog (back-end database)
    Code:
    SELECT MAX(ABC) AS ABC
         , MAX(XYZ) AS XYZ
         , MAX(PQR) AS PQR
         , ...
      FROM ( SELECT CASE WHEN name = 'ABC' then Value END AS ABC
                  , CASE WHEN name = 'XYZ' then Value END AS XYZ
                  , CASE WHEN name = 'PQR' then Value END AS PQR
                  , ...
               FROM daTable ) AS pivot
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    which version of sql server?
    2000 -- see post #1, first sentence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2008
    Posts
    11
    Thanks for the reply.

    But what if i dont know the number of entries or there are more than 100 entries in table ?

    I cant hardcode all the names then.

    More help would be appreciated in this regard.

    Regards
    - AVD

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AVD
    But what if i dont know the number of entries or there are more than 100 entries in table ?

    I cant hardcode all the names then.
    this is why i asked you in advance if you knew how many names there are

    and you said
    1) Yes, i know in advance how many names are there.
    so, yes, you can hardcode them

    if you can't, then you're just going to have to tell the developers to do what they should be doing in the first place
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2008
    Posts
    11
    Yes i do say yes to it.
    But if need arises to increase the entries in table then .....

    So , is there no way to display the result by using query :-(

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by AVD
    1) 2)This is the demand of the developers to show data by using query , i dont work in frontend so i dont understand application layer.
    Tell the developers that the database's job is to store and deliver the data. It is their job to format it for display. Pivoting is a display issue.
    Put your foot down. What they are asking you to do is bad design.
    And tell 'em the blindman told you so.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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