Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    20

    Unanswered: Need URGENT HELP on CTE

    Hello

    I need urgent help on CTE. Below is the table structure that we have:

    Tab_1:
    --------
    ID AGE GENDER
    ---------------------------------------------------
    2007022022001 33 M
    2005059142706 45 F

    This table Tab_1 needs to be converted as below:

    Age = 1
    Gender = 2
    M = MALE
    F = FEMALE

    Tab_2:
    --------
    ID ATTRIBUTE_ID VALUE
    -----------------------------------------------------
    2007022022001 1 33
    2007022022001 2 MALE
    2005059142706 1 45
    2005059142706 2 FEMALE

    Business Logic:

    There will be two records in the new table for the same ID number. The number 1 will denote the Age and the number 2 will denote the Gender.

    It's very urgent, can anyone please help. Need to do this today itself.

    Thanks & Regards
    Satyajit

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    No CTE may be not necessary.
    What version/release/fixpack and plaform OS are you using?

    Here is an example tested on DB2 9.7.5 for Windows.

    Note 1: I used CTE Tab_1 to generate test data easily.
    It is not necessary, if you provided CREATE TABLE Tab_1 statement and INSERT statement to populate the table.
    Note 2: If you executed the query on other environment, slight amendments might be necessary.

    Test data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Tab_1
    ( id , age , gender ) AS (
    VALUES
      ( '2007022022001' , 33 , 'M' )
    , ( '2005059142706' , 45 , 'F' )
    )

    Query and resunlt:
    Code:
    SELECT id
         , attribute_id
         , CASE attribute_id
           WHEN 1 THEN
                CHAR(age)
           WHEN 2 THEN
                CASE gender
                WHEN 'F' THEN 'FEMALE'
                WHEN 'M' THEN 'MALE'
    /*
                WHEN '?' THEN '?????'
                ELSE          '?????'
    */
                END
           END  AS value
     FROM  Tab_1
     CROSS JOIN
           (VALUES 1 , 2 ) AS f(attribute_id)
     ORDER BY
           id           DESC
         , attribute_id ASC
    ;
    ------------------------------------------------------------------------------
    
    ID            ATTRIBUTE_ID VALUE      
    ------------- ------------ -----------
    2007022022001            1 33         
    2007022022001            2 MALE       
    2005059142706            1 45         
    2005059142706            2 FEMALE     
    
      4 record(s) selected.
    Note 3: If you didn't specify ORDER BY clause, order of result could't be guaranteed.
    Last edited by tonkuma; 10-31-14 at 07:39.

Tags for this Thread

Posting Permissions

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