Using CTE in IBM DB2 with Pivot

Common Table Expression is an alternative which saves memory consumed when we use temporary tables in SQL SPROC.

I had a situation where I had to create a pivot with CTE and insert them into output parameters with a single input parameter.

WITH TABLE_NAME_CTE AS (

    select   ROWNUMBER() OVER() AS ROWNUM ,

    COLUMN_1

    AS COLUMN_A

    from    TABLE where

    PRIMARY_KEY_COLUMN = INPUT_PARAMETER

    FETCH FIRST 10 ROWS ONLY)

    SELECT

    COALESCE(max(case when ROWNUM = '1' then COLUMN_1 end),' '),

    COALESCE(max(case when ROWNUM = '2' then COLUMN_1 end),' ')

    into 

    OUTPUT_PARAMETER_1,

    OUTPUT_PARAMETER_2,

    from

    TABLE_NAME_CTE;

The Select statement is as below:

ROW_NUM            COLUMN_A
     1                        VALUE 1
     2                        VALUE 2




The Output of this code will be:

          OUTPUT_PARAMETER_1    OUTPUT_PARAMETER_2

               VALUE 1                                  VALUE 2

SELECT

COALESCE(max(case when ROWNUM = '1' then COLUMN_1 end),' '),

COALESCE(max(case when ROWNUM = '2' then COLUMN_1 end),' ')

from

TABLE_NAME_CTE;

Gives the pivoted output in this code.