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