TekBytz

I have come across a situation where I need to migrate a ORACLE query which has a pivot function to DB2 equivalent SQL. The Pivot function is not available in DB2 or DASHDB. so was no direct solution in the internet and hence came up with my own solution

Sample Oracle Query with pivot function :

SELECT NM,DESC,ADM,USR,SUP
FROM
(
SELECT NM, DESC, GRP
FROM SRC_GRP
)
PIVOT
(
COUNT(GRP)
FOR GRP IN
(
'Admin' as ADM,
'User' as USR,
'Support' as SUP
)
)
ORDER BY NM

Converted equivalent DB2/DASHDB query:

SELECT NM,DESC,count(ADM) AS ADM,count(USR) AS USR,count(SUP) AS SUP
FROM
(
SELECT NM, DESC,
CASE WHEN GRP = 'Admin' THEN GRP END AS ADM,
CASE WHEN GRP = 'User' THEN GRP END AS USR,
CASE WHEN GRP = 'Support' THEN GRP END AS SUP
FROM SRC_GRP
)
group by NM, DESC
Order by NM

I was able to get the same output from both the queries.