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).
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
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
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:
PARM | DESC |
---|---|
sel_stmt | Any valid SELECT statement for source data generation |
row_cols | Comma separated list of column names used in the GROUP BY of final statement |
col_col | A column name to pivot |
agg_col | A column name to aggregate |
agg_fn | Any valid Db2 aggregation function for the column name in "agg_col" parameter |
tmp_tbl | DGTT name for intermediate result |
null_ind | Null indicator |
rc | Return code (OUT) |
msg | Message text (OUT) |
stmt | The final SELECT generated (OUT) |
IBM DB2 PIVOT A TABLE FULL OF DATES
As months are a known quantity you could use a sum of a case statement:
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;
That will give you output similar to this:
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
Related Topics
Oracle Joins - Comparison Between Conventional Syntax VS Ansi Syntax
What Are the Use Cases for Selecting Char Over Varchar in SQL
Custom Date/Time Formatting in SQL Server
Select Data from Date Range Between Two Dates
SQL Server Replace, Remove All After Certain Character
SQL Statement to Get Column Type
Query for Element of Array in JSON Column
What's the Best to Check If Item Exist or Not: Select Count(Id)Or Exist(...)
How to Reorder Rows in SQL Database
Dynamic SQL to Generate Column Names
Postgresql Sequence Based on Another Column
How to Check Which Locks Are Held on a Table
How to Create Table Using Select Query in SQL Server
How to Count Occurrences of a Column Value Efficiently in SQL
Creating Temporary Tables in SQL
How to Version Your Database Schema
How to Use SQL's Getdate() and Dateadd() in a Linq to SQL Expression