Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: SELECT CASE WHEN problem

    Hello !
    For MS SQL 2000/2500

    I am having a table ::

    User
    Ident int NOT NULL,
    Numb int NOT NULL,
    Info nvarchar(100) NOT NULL,


    with this query ::

    SELECT Numb, Info, Ident
    FROM Users
    WHERE (Numb IN (100,200, 300))
    ORDER BY Ident


    I get ::

    Numb | Info | Ident

    100 | dgwrg | 1
    200 | dgeth yiuyi | 1
    300 | Agt hgn ngtn | 1

    100 | jttytujky | 2
    200 | yjtut iuiu | 2
    300 | dgd 2gggwekew | 2



    what I need is something like::

    SELECT
    (CASE WHEN Users.Numb = 100 THEN Users.Info END) AS InfoA,
    (CASE WHEN Users.Numb = 200 THEN Users.Info END) AS InfoB,
    (CASE WHEN Users.Numb = 300 THEN Users.Info END) AS InfoC,
    Ident
    FROM Users
    WHERE Ident = Ident
    (for the same Ident)

    to get ::

    InfoA | InfoB | InfoC | Ident

    dgwrg | dgeth yiuyi | Agt hgn ngtn | 1

    jttytujky | yjtut iuiu | dgd 2gggwekew | 2



    Actually I am getting a NULL value everywhere
    of course I cannot use MAX(CASE WHEN ... , or I am getting only a few rows and not all

    thanks a ton for helping

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by anselme
    of course I cannot use MAX(CASE WHEN ... , or I am getting only a few rows and not all
    Ooh - you were so close and then dismissed the answer.

    Code:
    SELECT
    MAX(CASE WHEN Users.Numb = 100 THEN Users.Info END) AS InfoA
    , MAX(CASE WHEN Users.Numb = 200 THEN Users.Info END) AS InfoB
    , MAX(CASE WHEN Users.Numb = 300 THEN Users.Info END) AS InfoC
    , Ident
    FROM Users
    GROUP BY Ident 
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    but with MAX I get only one row per Ident
    and in my table they are up to 10 rows for the same ident

    or my exemple was not good maybe, i cant get

    dgwrg | dgeth yiuyi | Agt hgn ngtn | 1

    jttytujky | yjtut iuiu | dgd 2gggwekew | 2
    czy | yccaiu |ddd | 2
    uyfghjj | fff hh | duuufff | 2


    thank you

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by anselme
    my exemple was not good
    Indeed

    Does your table have a primary key? What is it?
    Why would you get:
    Code:
    jttytujky | yjtut iuiu | dgd 2gggwekew | 2
    czy | yccaiu |ddd | 2
    uyfghjj | fff hh | duuufff | 2
    and not (for example)
    Code:
    czy | yjtut iuiu |duuufff  | 2
    jttytujky | yccaiu |ddd | 2
    uyfghjj | fff hh | dgd 2gggwekew | 2
    ?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    Ident is not the primary key

    in your exemple you have change the sort order
    the only think I need is all the rows (no matter the order) , with MAX I get one and only one row for each Ident
    it doesnt work

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I know Ident can't be the primary key. I wanted to know what is the key.

    I have changed the order - have you ever seen Brett's signature? In the absence of an order by clause order is arbitary and meaningless. Hence I was trying to establish meaning to the order you specified.

    Max fails to work whilst you are grouping on repeating sets. In the original sample data there were no repeating sets. This loops back to the primary key question
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2005
    Posts
    266
    there is no key in that table, and I cannot add one (it is not my table and another application is using it)

    i must get for each Ident (a kind of key number) as many rows as necessary and not only one MAX


    thank you

Posting Permissions

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