1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Merging multiple rows to a single result row using the Case Clause

Discussion in 'SQL PL/SQL' started by OldSchoolCoder, Jul 25, 2014.

  1. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    In my table I have Ids that have various types. Each ID will always have Type ‘A’, but not always Type B and Type C. My result set needs to merge the data and look like this with 4 separate columns:
    ID Prim_Ty Sec_Ty Ter_Ty
    Sam 11111 22222 33333
    Tim 44444 55555 (Spaces)
    Bob 77777 88888 (Spaces)
    Ray 99999 (Spaces) 12121


    However, my query separates results giving me too many rows like this:

    ID Prim_Ty Sec_TY Ter_Ty
    Sam 11111 (spaces) (Spaces)
    Sam (Spaces) 22222 (Spaces)
    Sam (Spaces) (Spaces) 33333
    Tim 4444 (Spaces) (Spaces)
    Tim (Spaces) 55555 (Spaces)
    BOB 77777 (Spaces) (Spaces)
    BOB (Spaces) 88888 (Spaces)
    Ray 99999 (Spaces) (Spaces)
    Ray (Spaces) (Spaces) 12121

    This is the data in the ID_TYPE table:

    ID TYPE VALUE
    Sam A 11111
    Sam B 22222
    Sam C 33333
    Tim A 44444
    Tim B 55555
    Bob A 77777
    Bob B 88888
    Ray A 99999
    Ray C 12121

    How should I modify my query to get the desired results?
    Code (SQL):
    SELECT DISTINCT (ID),
               CASE
                   WHEN TYPE = A THEN VALUE
               END   AS Prim_TY           CASE
                   WHEN TYPE = B THEN VALUE
                END  AS Sec_TY
                CASE
                    WHEN TYPE = C THEN VALUE
                END  AS Ter_TY
    FROM ID_TYPE;
    Thank you in advance for getting me straightened out!
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    There are a couple of ways of doing it; using your approach (you were quite close to solution):

    Code (SQL):
    SELECT  id,
      MAX(CASE
        WHEN TYPE = 'A' THEN VALUE
        ELSE NULL
      END) AS Prim_TY,
      MAX(CASE
        WHEN TYPE = 'B' THEN VALUE
        ELSE NULL
      END) AS Sec_TY,
      MAX(CASE
        WHEN TYPE = 'C' THEN VALUE
        ELSE NULL
      END) AS Ter_TY
    FROM id_type
    GROUP BY id
    ;
    Note: I'm taking MAX() to get the MAX of the VALUE if there is more than one value for a given ID and TYPE. You may use SUM or MIN function, depends on your requirement of course.

    If you have 11g, you can use PIVOT - more elegant to achieve the results, see link:

    http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php
     
  3. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Thank you for your help, Rajen. I'm still getting the same exact results. There isn't a need for Max as there's never more than 1 value per type. The only thing I can determine is that it has something to do with the other tables involved. I'll work on it some more and see if I can figure out what's going on.
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Even if you have 1 value per type, you need to have an aggregate function (in this case MAX) so as to GROUP BY id and do the trick
     
  5. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Thanks again, Rajen. Even with the group by, I can't get those types on the same line. It's maddening! Is it possible that I need to do some sort of nested Select?
    Lynda
     
  6. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Methinks you want something along these lines :

    Code (SQL):
    WITH pivot_data AS
         (SELECT 'SAM' AS "NAME", 'A' AS "TYPE", 1 AS "VAL" FROM dual
          UNION ALL
          SELECT 'SAM' AS "NAME", 'B' AS "TYPE", 2 AS "VAL"  FROM dual
          UNION ALL
          SELECT 'SAM' AS "NAME", 'C' AS "TYPE", 3 AS "VAL"  FROM dual
          UNION ALL
          SELECT 'TIM' AS "NAME", 'A' AS "TYPE", 4 AS "VAL"  FROM dual
          UNION ALL
          SELECT 'TIM' AS "NAME", 'B' AS "TYPE", 5 AS "VAL"  FROM dual
          UNION ALL
          SELECT 'BOB' AS "NAME", 'A' AS "TYPE", 6 AS "VAL"  FROM dual
          UNION ALL
          SELECT 'BOB' AS "NAME", 'B' AS "TYPE", 7 AS "VAL"  FROM dual
          UNION ALL
          SELECT 'RAY' AS "NAME", 'A' AS "TYPE", 8 AS "VAL"  FROM dual
          UNION ALL
          SELECT 'RAY' AS "NAME", 'C' AS "TYPE", 9 AS "VAL"  FROM dual)
    SELECT * FROM pivot_data
    PIVOT ( COUNT(VAL) FOR TYPE IN ('A', 'B', 'C') );
    Which will give you this :

    [TABLE]NAME 'A' 'B' 'C'
    TIM 1 1 0
    RAY 1 0 1
    BOB 1 1 0
    SAM 1 1 1 [/TABLE]

    I'm really, really new w/ Pivot's as I've been stuck in an 8i world...for...freaking....ever.

    HTH

    CJ

    :hurray
     
  7. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Well....that SUPPOSED to be :

    NAME.....'A'.....'B'.....'C'
    TIM........ 1.......1......0
    RAY.........1.......0......1
    BOB........1.......1.......0
    SAM........1.......1.......1

    Apologies for the dots...I think local policy is screwing w/ the advanced editor options.

    ...sigh
     
  8. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    CJ, Thanks for the help. I hope I'm following you correctly... the actual existing table I'm working with has around 500,000 records in it so doing the union/pivot would be out of the question.
    Lynda
     
  9. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Lynda,

    With the sample you gave it does work; so there must be something else in your data or table structure (apart from the no. of records) which is creating this issue.

    Can you please provide a more representative sample where you can reproduce the error with the SQL I posted ... of course, "hiding" any confidential information you may have ...

    Thanks...
     
  10. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The SQL for the example you provided follows. I started with Oracle SQL before it supported CASE, so I tend to think in terms of DECODE for operations like this. I created your test case (columns renamed simply because I don't like using reserved words). The DECODE example would be:

    Code (Text):
    CREATE TABLE id_type (
    id_col     VARCHAR2(5),
    type_col   VARCHAR2(2),
    value_col  VARCHAR2(10));

    INSERT INTO id_type VALUES ('Sam', 'A', '11111');
    INSERT INTO id_type VALUES ('Sam', 'B', '22222');
    INSERT INTO id_type VALUES ('Sam', 'C', '33333');
    INSERT INTO id_type VALUES ('Tim', 'A', '44444');
    INSERT INTO id_type VALUES ('Tim', 'B', '55555');
    INSERT INTO id_type VALUES ('Bob', 'A', '77777');
    INSERT INTO id_type VALUES ('Bob', 'B', '88888');
    INSERT INTO id_type VALUES ('Ray', 'A', '99999');
    INSERT INTO id_type VALUES ('Ray', 'C', '12121');

    SELECT id_col,
           MAX(DECODE(type_col, 'A', value_col, NULL)) AS TYPE_A,
           MAX(DECODE(type_col, 'B', value_col, NULL)) AS TYPE_B,
           MAX(DECODE(type_col, 'C', value_col, NULL)) AS TYPE_C
    FROM   id_type
    GROUP BY id_col;

    ID_COL TYPE_A     TYPE_B     TYPE_C  
    ------ ---------- ---------- ----------
    Ray    99999                 12121      
    Bob    77777      88888                
    Sam    11111      22222      33333      
    Tim    44444      55555
    If you prefer CASE, or simply want ANSI-compliant SQL, the following query will return the same result:

    Code (Text):
    SELECT id_col,
           MAX(CASE type_col
                 WHEN 'A' THEN value_col
                 ELSE NULL
               END)            AS TYPE_A,
           MAX(CASE type_col
                 WHEN 'B' THEN value_col
                 ELSE NULL
               END)            AS TYPE_B,
           MAX(CASE type_col
                 WHEN 'C' THEN value_col
                 ELSE NULL
               END)            AS TYPE_C
    FROM   id_type
    GROUP BY id_col;
     
  11. Rajesh Sinha

    Rajesh Sinha Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    HYderabad
    You can Try this :
    select ID,sum(case
    when type = 'A' then
    value
    End) Prim_Ty,
    sum(case
    when type = 'B' then
    value
    End) Sec_Ty,
    sum(case
    when type = 'C' then
    value
    End) Ter_Ty
    from temp4
    group by ID
     
  12. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    The actual query is rather complicated and involves 8 other tables, so I'm going to try to find a local person to help me figure this one out. If I discover anything that would be of additional assistance to another coder, I'll post it.

    I appreciate your assistance very much! Thank you.
     
  13. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    As an FYI - I could've used LISTAGG to solve this, but I found that using joins for each type worked too. Thank you for all the help!