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

sirlunk · #1 (**permalink**) 02-02-12, 11:10

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.

tonkuma · #2 (**permalink**) 02-02-12, 15:43

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.

tonkuma · #3 (**permalink**) 02-02-12, 16:08

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

sirlunk · #4 (**permalink**) 02-03-12, 10:00

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

Internet Services

Web Development

Digital Marketing

Consumer Tech