How to Pivot a Table in Db2

How can I Pivot a table in DB2?

If your version doesn't have DECODE(), you can also use this:

INSERT INTO B (id, code1_val, code2_val, code3_val)  
WITH Ids (id) as (SELECT DISTINCT id
                  FROM A) -- Only to construct list of ids

SELECT Ids.id, a1.value, a2.value, a3.value
FROM Ids -- or substitute the actual id table
JOIN A a1
     ON a1.id = ids.id
        AND a1.code = 1
JOIN A a2
     ON a2.id = ids.id
        AND a2.code = 2
JOIN A a3
     ON a3.id = ids.id
        AND a3.code = 3

(Works on my V6R1 DB2 instance, and have an SQL Fiddle Example).

DB2 Pivot (rows to columns)

Solution 1

select ServerName,
       sum(case when JobStatus='Success' then 1 else 0 end) Success,
       sum(case when JobStatus='Failure' then 1 else 0 end) Failure
from yourtable
group by ServerName

Pivoting in DB2

It's not very pretty, but it should work. DB2 doesn't have a built-in PIVOT function, like SQL Server.

SELECT DISTINCT
     A.ItemID
    ,(SELECT value
      FROM table B
      WHERE B.ItemID = A.ItemID
        AND B.Item   = 'Meeting'
    ) AS Meeting
    ,(SELECT value
      FROM table B
      WHERE B.ItemID = A.ItemID
        AND B.Item   = 'Advise'
    ) AS Advise
    ,(SELECT value
      FROM table B
      WHERE B.ItemID = A.ItemID
        AND B.Item   = 'NoAdvise'
    ) AS NoAdvise
FROM table A

dynamic pivot SQL Query in DB2

You may try the following generic Stored Procedure doing pivoting.

--#SET TERMINATOR @
create or replace procedure pivot
(
  in  sel_stmt varchar(4000)
, in  row_cols varchar(200)
, in  col_col  varchar(128)
, in  agg_col  varchar(128)
, in  agg_fn   varchar(10)
, in  tmp_tbl  varchar(128)
, in  null_ind varchar(10)
, out rc       int
, out msg      varchar(128)
, out stmt     varchar(4000)
)
LANGUAGE SQL
DYNAMIC RESULT sets 1
BEGIN
 declare QUOT1        char(1) default '''';
 declare QUOT2        char(1) default '"';
 declare SQLCODE      int default 0;
 declare SQLTYPE_ID   int;
 declare SQLTYPE      varchar(128);
 declare SQLLENGTH    int;
 declare SQLSCALE     int;
 declare SQLNAME_DATA varchar(128);
 declare SQLTYPEF     varchar(128);
 declare col_val      varchar(4000);
 declare apo          varchar(1);
 declare l1 RESULT_set_LOCATOR VARYING;

 declare c2 cursor for s2;
 declare c_out cursor with return for s_out;

 declare EXIT HANDLER FOR SQLEXCEPTION
 BEGIN
   GET DIAGNOSTICS EXCEPTION 1 MSG = MESSAGE_TEXT;
   set RC = SQLCODE;
 END;

 set col_col=upper(col_col);
 set agg_col=upper(agg_col);
 -- insert result of select statement into temp table
 set stmt = 'describe '||sel_stmt;
 call SYSPROC.ADMIN_CMD(stmt);
 set stmt = '';
 associate result set locator (l1)
 with procedure SYSPROC.ADMIN_CMD;
 allocate c1 cursor for result set l1;
 --open c1;
 fetch c1 into SQLTYPE_ID, SQLTYPE, SQLLENGTH, SQLSCALE, SQLNAME_DATA;
 while (SQLCODE!=100) do
   set SQLTYPEF = SQLTYPE
   ||case 
      when SQLTYPE IN ('DECIMAL', 'DECFLOAT', 'CHARACTER', 'VARCHAR') then 
         '('||RTRIM(CHAR(SQLLENGTH))
       ||case when SQLTYPE='DECIMAL' then ','||RTRIM(CHAR(SQLSCALE)) else '' end
       ||')'                                
      else ''
     end;
   if (col_col=SQLNAME_DATA) then
     set apo = 
      case 
       when SQLTYPE in ('DECIMAL', 'DECFLOAT', 'INTEGER', 'SMALLINT', 'BIGINT', 'REAL', 'DOUBLE') then '' 
       else QUOT1 
      end;
   end if;
   set stmt = stmt||', '||SQLNAME_DATA||' '||SQLTYPEF;
   fetch c1 into SQLTYPE_ID, SQLTYPE, SQLLENGTH, SQLSCALE, SQLNAME_DATA;
 end while;
 close c1;
 set stmt = 
   'declare global temporary table '||tmp_tbl||'('||substr(stmt, 3)
 ||') with replace on commit preserve rows not logged';
 execute immediate stmt;
 set stmt = 'insert into '||tmp_tbl||' '||sel_stmt;
 execute immediate stmt;

 -- construct select statement
 set stmt = 'select distinct rtrim(char('||col_col||')) from '||tmp_tbl||' order by 1';
 prepare s2 from stmt;
 set stmt='';
 open c2;
 fetch c2 into col_val;
 while (SQLCODE!=100) do
   set stmt = 
     stmt||', '||agg_fn||'('
   ||'case when '||col_col||' '
   ||case when col_val is null then 'IS NULL' else ('='||apo||replace(col_val, QUOT1, QUOT1||QUOT1)||apo) end
   ||' then '||agg_col||' end) as '||QUOT2||coalesce(replace(col_val, QUOT2, QUOT2||QUOT2), null_ind)||QUOT2;
   fetch c2 into col_val;
 end while;
 close c2;
 -- add to the select statement groups
 set row_cols = nullif(row_cols, '');
 set stmt = 
   'select '||case when row_cols is not null then row_cols||',' else coalesce(row_cols, '') end
 ||substr(stmt, 2)||' from '||tmp_tbl||' '
 ||case when row_cols is not null then ('group by '||row_cols||' order by '||row_cols) else '' end;
 -- execute this statement
 prepare s_out from stmt;
 open c_out;
END@

Parameter description:

select year(datecol) as year
  ,sum(case when month(datecol) = 1 then 1 else 0 end) as jan
  ,sum(case when month(datecol) = 2 then 1 else 0 end) as feb
  ,sum(case when month(datecol) = 3 then 1 else 0 end) as mar
  ,sum(case when month(datecol) = 4 then 1 else 0 end) as apr
  ,sum(case when month(datecol) = 5 then 1 else 0 end) as may
  ,sum(case when month(datecol) = 6 then 1 else 0 end) as jun
  ,sum(case when month(datecol) = 7 then 1 else 0 end) as jul
  ,sum(case when month(datecol) = 8 then 1 else 0 end) as aug
  ,sum(case when month(datecol) = 9 then 1 else 0 end) as sep
  ,sum(case when month(datecol) = 10 then 1 else 0 end) as oct
  ,sum(case when month(datecol) = 11 then 1 else 0 end) as nov
  ,sum(case when month(datecol) = 12 then 1 else 0 end) as dec
 from datetest 
 group by year(datecol)
 order by 1;

YEAR        JAN         FEB         MAR         APR         MAY         JUN         JUL         AUG         SEP         OCT         NOV         DEC
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
       2018           0           0           0           0           0           0           0           0           0           0           3           0
       2019           0           0           0           0           0           0           0           0           0           1           2           0
       2020           0           0           0           0           0           0           0           0           0           1           1           0
       2021           0           0           0           0           0           0           0           0           0           2           6           0