DB2 Pivot Table – 1

A colleague of mine asked me how to convert values on multiple rows into a single column.  The search took me to IT toolbox.  Here is how you would do it:
Suppose  I have a table with rows like this :

NAME       SKILL
———- ——————–
raju         db2
raju         sql server
raju         oracle
raju         Db2 Z/os
raju         mysql
raju         php
james      php
james      perl
james      c++
james      c#
erik         ajax
erik         java

and I want the result sets to look like this :

Name      Skills

erik          ajax,java
james      php,perl,c++,c#
raju         db2,sql server,oracle,Db2 Z/os,mysql,php

This sql builds a pivot table and uses XML functions to convert the rows to XML tags and then a simple REPLACE converts the tags to a comma and the substr function on the outer select cuts out the last comma :

SELECT NAME,
SUBSTR(skill_list, 1, LENGTH(skill_list) -1) as “Skills”
FROM
(SELECT name,
REPLACE
(REPLACE
(XMLSERIALIZE
(CONTENT XMLAGG(XMLELEMENT(Name “A”, skill))
AS VARCHAR(120)),
‘<A>’, ”),
‘</A>’, ‘,’) AS skill_list
FROM skills
GROUP BY name) AS X;

Its pretty slick, this was adapted from a presentation by Serge Rileau

About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...
This entry was posted in db2, how to and tagged , , , , , , , . Bookmark the permalink.