Results 1 to 4 of 4

Thread: query required

  1. #1
    Join Date
    Jun 2010
    Posts
    1

    Question Unanswered: query required

    i need a query which processes input like

    KEY ATTRIBUTE1 ATTRIBUTE2
    K1 A11
    K1 A12
    K1 A21
    K1 A22
    K1 A23



    and the output should be like

    Output
    KEY ATTRIBUTE1 ATTRIBUTE2
    K1 A11 A21
    K1 A12 A22
    K1 A23



    Some more details on the Data:
    a) At least one of the attributes would always be present.
    b) Any combination and number of occurrences of the attributes (Attribute1 and Attribute2) is possible.
    c) There would be multiple values in the Key column (i.e. K1, K2, etc).
    d) There is no correlation between the values of Attribute1 and Attribute2.
    e) We need to bring all non-space and non-null values for every column in ascending order.
    f) Though primary audience is DB2, we can use any other RDBMS.
    Last edited by siddhartha_tiwari; 06-17-10 at 00:07.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although syntax of VALUES clause used in the common table expression(input) may be valid only on DB2 for LUW,
    it will be easy to modify it to conform with your DBMS.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     input(key , attribute1 , attribute2) AS (
    VALUES
      ('K1' , 'A11' , '')
    , ('K1' , 'A12' , '')
    , ('K1' , ''    , 'A21')
    , ('K1' , CAST(NULL AS VARCHAR(1)) , 'A22')
    , ('K1' , ''    , 'A23')
    , ('K2' , 'A21' , '')
    , ('K3' , ''    , 'A31')
    , ('K3' , ''    , 'A32')
    )
    SELECT COALESCE(s1.key , s2.key) AS key
         , attribute1
         , attribute2
      FROM (SELECT key , attribute1
                 , ROW_NUMBER()
                     OVER(PARTITION BY key
                              ORDER BY attribute1
                         ) rn
              FROM input
             WHERE attribute1 IS NOT NULL
               AND attribute1 <> ''
           ) AS s1
      FULL OUTER JOIN
           (SELECT key , attribute2
                 , ROW_NUMBER()
                     OVER(PARTITION BY key
                              ORDER BY attribute2
                         ) rn
              FROM input
             WHERE attribute2 IS NOT NULL
               AND attribute2 <> ''
           ) AS s2
       ON  s2.key = s1.key
       AND s2.rn  = s1.rn
     ORDER BY
           key
         , COALESCE(s1.rn , s2.rn)
    ;
    ------------------------------------------------------------------------------
    
    KEY ATTRIBUTE1 ATTRIBUTE2
    --- ---------- ----------
    K1  A11        A21       
    K1  A12        A22       
    K1  -          A23       
    K2  A21        -         
    K3  -          A31       
    K3  -          A32       
    
      6 record(s) selected.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The predicates "attribute1 IS NOT NULL" and "attribute2 IS NOT NULL" are not neccesary,
    bacause if attribute1 was NULL then the predicate "attribute1 <> '' " would be unknown and the row would not be selected.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    INNER JOIN may be enough.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     input(key , attribute1 , attribute2) AS (
    VALUES
      ('K1' , 'A11' , '')
    , ('K1' , 'A12' , '')
    , ('K1' , ''    , 'A21')
    , ('K1' , CAST(NULL AS VARCHAR(1)) , 'A22')
    , ('K1' , CAST(NULL AS VARCHAR(1)) , 'A23')
    , ('K2' , 'B11' , CAST(NULL AS VARCHAR(1)))
    , ('K2' , 'B12' , '')
    , ('K3' , ''    , 'C21')
    )
    SELECT s1.key
         , attribute1
         , attribute2
      FROM (SELECT key
                 , attribute1
                 , ROW_NUMBER()
                     OVER(PARTITION BY key
                              ORDER BY NULLIF(attribute1 , '')
                         ) AS rn
              FROM input
           ) AS s1
      INNER JOIN
           (SELECT key
                 , attribute2
                 , ROW_NUMBER()
                     OVER(PARTITION BY key
                              ORDER BY NULLIF(attribute2 , '')
                         ) AS rn
              FROM input
           ) s2
       ON  s2.key = s1.key
       AND s2.rn  = s1.rn
     WHERE attribute1 <> ''
       OR  attribute2 <> ''
     ORDER BY
           s1.key , s1.rn
    ;
    ------------------------------------------------------------------------------
    
    KEY ATTRIBUTE1 ATTRIBUTE2
    --- ---------- ----------
    K1  A11        A21       
    K1  A12        A22       
    K1             A23       
    K2  B11        -         
    K2  B12                  
    K3             C21       
    
      6 record(s) selected.
    Blanks and NULLs will be chosen arbitralily.

    If you want blanks for all null or blank values,
    you can use COALESCE(attribute<n> , '') AS attribute<n> in final SELECT list.
    If you want NULLs for all null or blank values,
    you can use NULLIF(attribute<n> , '') AS attribute<n> in final SELECT list.

Posting Permissions

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