Results 1 to 4 of 4

Thread: pivot to 1 row

  1. #1
    Join Date
    Oct 2005
    Posts
    92

    Unanswered: pivot to 1 row

    Hi All,

    my source data looks like this,

    ID product code count
    1 used 10
    1 new 11
    1 Base 5
    1 lead 3
    2 used 5
    2 Base 10

    the expected output is,

    ID usedquote newquote
    1 Y Y
    2 Y N

    i.e. ignore other product codes (base, lead) in the logic and also output.

    my logic is:

    CASE
    WHEN (PRODUCT_CD = 'used' AND count> 0)
    THEN
    'Y'
    ELSE
    'N'
    END
    usedquote,

    CASE
    WHEN (PRODUCT_CD = 'new' AND count > 0)
    THEN
    'Y'
    ELSE
    'N'
    END

    when i run the above i am getting this,

    the output i am getting is,

    ID product code usedquote newquote
    1 used Y N
    1 new N Y

    can someone please let me know how do i get to the expected result i.e.

    the expected output is,

    ID usedquote newquote
    1 Y Y
    2 Y N

    Thanks
    pavan

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try something like...

    Code:
    SELECT id
         , MAX(CASE ... END) AS usedquote
         , MAX(CASE ... END) AS newquote
     FROM  <your source data>
     GROUP BY
           id
    Note 1: Put your CASE expressions inside the MAX functions.
    Note 2: I assumed 'Y' > 'N', then I used MAX function.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Please try something like...
    but if no row exists with PRODUCT_CD=new, then there is no COUNT value for CASE to be applied
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Oct 2005
    Posts
    92

    pivot to 1 row

    Quote Originally Posted by tonkuma View Post
    Please try something like...

    Code:
    SELECT id
         , MAX(CASE ... END) AS usedquote
         , MAX(CASE ... END) AS newquote
     FROM  <your source data>
     GROUP BY
           id
    Note 1: Put your CASE expressions inside the MAX functions.
    Note 2: I assumed 'Y' > 'N', then I used MAX function.


    Thank you. it worked

Posting Permissions

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