Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Question Unanswered: DB2 PIVOT? (Dynamic Table Columns based on Distinct Values?)

    Hello,

    I'd like to know what DB2 SQL code could transform data from HISTORY_TABLE into REQUIRED_REPORT. COUNTRY_CODE represents the international dialling code of the country where the employee is based (e.g. 1 for USA, 44 for UK, 27 for South Africa).

    HISTORY_TABLE:
    MONTH, COUNTRY_CODE, NUM_USERS
    -------------------------------------
    Jan-2012,1,50
    Jan-2012,44,25
    Feb-2012,1,51
    Feb-2012,44,26
    Feb-2012,27,1

    REQUIRED_REPORT:
    MONTH,1,44,27
    ---------------
    Jan-2012,50,25,0
    Feb-2012,51,26,1

    Background:
    The history table doesn't exist yet. The dev team proposes a history table with unique columns for every known country code (e.g. MONTH,1,44,27). I think this is a terrible idea because someone needs to remember to add a new column whenever we hire a person from a new country. I've proposed the HISTORY_TABLE shown above, but the dev team tells me that they cannot transform (pivot?) the data into the required reporting format. They've agreed to revisit this if I can provide sample SQL code, hence this post.

    Unfortunately I know nothing about DB2/SQL, so apologies if this is dead easy or if my terminology is incorrect. I don't know if this is relevant, but the report will be emailed in CSV format to recipients.

    Thanks in advance for your assistance.
    Lunk

    ---------------------------
    As per sticky, DB2 info:
    C:\Program Files (x86)\IBM\SQLLIB\BIN>db2level
    DB21085I Instance "DB2_01" uses "32" bits and DB2 code release "SQL09074" with level identifier "08050107". Informational tokens are "DB2 v9.7.400.501", "s110330", "IP23237", and Fix Pack "4". Product is installed at "C:\PROGRA~2\IBM\SQLLIB" with DB2 Copy Name "DB2X86".

    C:\Program Files (x86)\IBM\SQLLIB\BIN>db2licm -l
    <blank>

    I believe DB2 is installed on a Solaris server.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    As you know, number of columns and column names of result of a query are fixed,
    if you don't use dynamic SQL(by using PREPARE and EXECUTE statement).

    An alternate idea is to fix the country columns in the query.
    And modify the query, if/when new country sould be included.

    Another alternative is to return country columns enough number of countries than expected,
    and return titles in the first row of the query result instead of column names.


    Here are examples of latter idea.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH 
      history_table
    ( month , country_code , num_users ) AS (
    VALUES
      ( 'Jan-2012' ,  1 , 50 )
    , ( 'Jan-2012' , 44 , 25 )
    , ( 'Feb-2012' ,  1 , 51 )
    , ( 'Feb-2012' , 44 , 26 )
    , ( 'Feb-2012' , 27 ,  1 )
    )
    --(
    SELECT 'Country Code -->' AS month
         , MAX( CASE rnum WHEN 1 THEN country_code END ) country_1
         , MAX( CASE rnum WHEN 2 THEN country_code END ) country_2
         , MAX( CASE rnum WHEN 3 THEN country_code END ) country_3
         , MAX( CASE rnum WHEN 4 THEN country_code END ) country_4
         , MAX( CASE rnum WHEN 5 THEN country_code END ) country_5
         , '      ' AS month_order
     FROM  (
           SELECT country_code
                , ROW_NUMBER()
                     OVER( ORDER BY country_code ) AS rnum
            FROM  history_table
            GROUP BY
                  country_code
           )
    UNION ALL
    SELECT month
         , MAX( CASE rnum WHEN 1 THEN num_users END ) country_1
         , MAX( CASE rnum WHEN 2 THEN num_users END ) country_2
         , MAX( CASE rnum WHEN 3 THEN num_users END ) country_3
         , MAX( CASE rnum WHEN 4 THEN num_users END ) country_4
         , MAX( CASE rnum WHEN 5 THEN num_users END ) country_5
         , SUBSTR(month , 5 , 4) ||
           CASE LEFT(month , 3)
           WHEN 'Jan' THEN '01'
           WHEN 'Feb' THEN '02'
           WHEN 'Mar' THEN '03'
           WHEN 'Apr' THEN '04'
           WHEN 'May' THEN '05'
           WHEN 'Jun' THEN '06'
           WHEN 'Jul' THEN '07'
           WHEN 'Aug' THEN '08'
           WHEN 'Sep' THEN '09'
           WHEN 'Oct' THEN '10'
           WHEN 'Nov' THEN '11'
           WHEN 'Dec' THEN '12'
           END AS month_order
     FROM  (
           SELECT h.*
                , DENSE_RANK()
                     OVER( ORDER BY country_code ) AS rnum
            FROM  history_table h
           )
     GROUP BY
           month
    --)
     ORDER BY
           month_order
    ;
    ------------------------------------------------------------------------------
    
    MONTH            COUNTRY_1   COUNTRY_2   COUNTRY_3   COUNTRY_4   COUNTRY_5   MONTH_ORDER
    ---------------- ----------- ----------- ----------- ----------- ----------- -----------
    Country Code -->           1          27          44           -           -            
    Jan-2012                  50           -          25           -           - 201201     
    Feb-2012                  51           1          26           -           - 201202     
    
      3 record(s) selected.
    If you want to use different order of countries,
    use CASE expressions in ORDER BY clauses in ROW_NUMBER and DENSE_RANK expressions.

    Example 2: Addition to reorder countries, make num_users to 0, if it was null and contry code was present.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH 
      history_table
    ( month , country_code , num_users ) AS (
    VALUES
      ( 'Jan-2012' ,  1 , 50 )
    , ( 'Jan-2012' , 44 , 25 )
    , ( 'Feb-2012' ,  1 , 51 )
    , ( 'Feb-2012' , 44 , 26 )
    , ( 'Feb-2012' , 27 ,  1 )
    )
    --(
    SELECT 'Country Code -->' AS month
         , MAX( CASE rnum WHEN 1 THEN country_code END ) country_1
         , MAX( CASE rnum WHEN 2 THEN country_code END ) country_2
         , MAX( CASE rnum WHEN 3 THEN country_code END ) country_3
         , MAX( CASE rnum WHEN 4 THEN country_code END ) country_4
         , MAX( CASE rnum WHEN 5 THEN country_code END ) country_5
         , '      ' AS month_order
     FROM  (
           SELECT country_code
                , ROW_NUMBER()
    --                 OVER( ORDER BY country_code ) AS rnum
                     OVER( ORDER BY CASE country_code
                                    WHEN   1 THEN    1
                                    WHEN  44 THEN    2
                                    WHEN  27 THEN    3
                                    ELSE           999 + country_code
                                    END
                         ) AS rnum
            FROM  history_table
            GROUP BY
                  country_code
           )
    UNION ALL
    SELECT month
         , MAX( CASE rnum WHEN 1 THEN num_users ELSE NULLIF( 1 , SIGN(1 - max_rnum) ) - 1 END ) country_1
         , MAX( CASE rnum WHEN 2 THEN num_users ELSE NULLIF( 1 , SIGN(2 - max_rnum) ) - 1 END ) country_2
         , MAX( CASE rnum WHEN 3 THEN num_users ELSE NULLIF( 1 , SIGN(3 - max_rnum) ) - 1 END ) country_3
         , MAX( CASE rnum WHEN 4 THEN num_users ELSE NULLIF( 1 , SIGN(4 - max_rnum) ) - 1 END ) country_4
         , MAX( CASE rnum WHEN 5 THEN num_users ELSE NULLIF( 1 , SIGN(5 - max_rnum) ) - 1 END ) country_5
         , SUBSTR(month , 5 , 4) ||
           CASE LEFT(month , 3)
           WHEN 'Jan' THEN '01'
           WHEN 'Feb' THEN '02'
           WHEN 'Mar' THEN '03'
           WHEN 'Apr' THEN '04'
           WHEN 'May' THEN '05'
           WHEN 'Jun' THEN '06'
           WHEN 'Jul' THEN '07'
           WHEN 'Aug' THEN '08'
           WHEN 'Sep' THEN '09'
           WHEN 'Oct' THEN '10'
           WHEN 'Nov' THEN '11'
           WHEN 'Dec' THEN '12'
           END AS month_order
     FROM  (
           SELECT h.*
                , DENSE_RANK()
    --                 OVER( ORDER BY country_code ) AS rnum
                     OVER( ORDER BY CASE country_code
                                    WHEN   1 THEN    1
                                    WHEN  44 THEN    2
                                    WHEN  27 THEN    3
                                    ELSE           999 + country_code
                                    END
                         ) AS rnum
                , MAX( DENSE_RANK() OVER( ORDER BY country_code ) )
                     OVER() AS max_rnum
            FROM  history_table h
           )
     GROUP BY
           month
    --)
     ORDER BY
           month_order
    ;
    ------------------------------------------------------------------------------
    
    MONTH            COUNTRY_1   COUNTRY_2   COUNTRY_3   COUNTRY_4   COUNTRY_5   MONTH_ORDER
    ---------------- ----------- ----------- ----------- ----------- ----------- -----------
    Country Code -->           1          44          27           -           -            
    Jan-2012                  50          25           0           -           - 201201     
    Feb-2012                  51          26           1           -           - 201202     
    
      3 record(s) selected.
    Last edited by tonkuma; 02-02-12 at 17:24. Reason: Outer most parentheses might be not neccesary. Add " + country_code" to ELSE clause in CASE country_code expressions.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The NULLIF expressions in the second subselect of Example 2 were equivalent to the CASE expressions, like ...
    Code:
    /*   , MAX( CASE rnum WHEN 5 THEN num_users ELSE NULLIF( 1 , SIGN(5 - max_rnum) ) - 1 END ) country_5 */
         , MAX( CASE rnum WHEN 5 THEN num_users ELSE CASE WHEN 5 <= max_rnum THEN 0 ELSE null END END ) country_5

  4. #4
    Join Date
    Feb 2012
    Posts
    2

    Thank you!

    Hi Tonkuma

    Thanks so much for your input. I'm going to try and make sense of this and I'll come back to you if I have any questions.

    Thanks again.

    Lunk

Posting Permissions

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