Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Unanswered: Help with crosstab (was "Query Help Needed!")

    Hey,

    i have a table which has the foll data:

    employeecode Amount AmountDescription
    1 100 x
    2 200 y
    3 150 x
    4 300 z

    now i need to fetch this data such that i can display the output as :

    empcode x y z
    1 100
    2 200
    3 150
    4 300

    any suggestions?????????

    platform: SQL Server 2000


    thanx!

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    sorry, no suggestions..... unless we know

    1) why do u need it (the practical scenario)
    2) how do u ensure that the string "x" fits a column name
    3) how do u ensure that the number of columns is within the max limit for select/table
    4) what would be the value against row 1, col x of the output

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think this will do what you want.
    Code:
    SELECT empcode
     , Max(CASE WHEN AmountDescription = 'x' THEN amount ELSE 0 END) As 'x'
     , Max(CASE WHEN AmountDescription = 'y' THEN amount ELSE 0 END) As 'y'
     , Max(CASE WHEN AmountDescription = 'z' THEN amount ELSE 0 END) As 'z'
    FROM MyTable
    GROUP BY empcode
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, george, that will put 0s where they didn't exist in the data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    just tweak that last response to use null values instead of 0's

    select empcode, case when AmountDescription = 'x' then amount else null end as X,
    Case when AmountDescription = 'y' then amount else null end as Y,
    Case when AmountDescription = 'z' then amount else null end as Z
    from MyTable
    Group by Empcode

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but don't lose your MAXes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Max means that it becomes aggregated and doesn't have to be used in the GROUP BY clause
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ppr07
    i need to fetch this data such that i can display the output as :
    empcode x y z
    1 100
    2 200
    3 150
    4 300
    Code:
    SELECT empcode
    
    , Amount , x = NULL
    , y = NULL , z = NULL FROM MyTable



    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    <sigh />

    pootle, please forgive the lack of proper spacing in the original post

    this is what was intended (and you can see this if you open up the original post in Edit) --
    Code:
    empcode   x        y        z 
    1         100        
    2                  200
    3         150       
    4                           300
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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