May
15
2015 Posted by Mark Gillis

Pivoting and Unpivoting data with pureXML

I am sometimes presented with data that, although stored in the relational database, still needs some ‘massaging’ to be meaningful. An issue that would fall into that category would be where a set of values has been stored in a string; in order for this to be usable I really need to “unpivot” it so that I have a result set composed of distinct values.

Let’s look at an example. Here is a simple table with a character group ID and a string of values stored as a VARCHAR(100).

Logbook groups

The values are really integers; primary keys to another table and I want those available as a set of unique values.

In the past I have addressed this problem with recursive SQL and there’s nothing wrong with that, except that it is a bit cumbersome to maintain. If I wanted some SQL to render these strings of values as a result set of single keys, I’d need something like this (I’m just querying two of the smaller groups to avoid flooding the blog with results):

Select distinct

That does give the required result set

feature order

but it’s a tricky bit of SQL to maintain.

A better option might be to exploit the XML capabilities that come as part of the DB2 package anyway. A nice compact piece of SQL will cast the input string as an XML document and then exploit XMLTABLE to return the values as a results set

Select

If this SQL is embedded in a view

Creat or replace

then the query can be executed against that to get the same result set

select from

This query is, in my humble opinion, more intuitive and therefore easier to maintain. It also performs well when combined with other relational data. For instance, if the above query is used as the basis for a view (UNPIVOTED_KEYS) it can be combined with the detailed data from the source table to expand on the individual keys with enriched data

Flight date

Registration airfield

But what about the opposite problem? Imagine that some kind soul has decided to store a long and reasonably complex set of data across several rows and you need to collate or pivot it into a single entity? Well pureXML can help you out there too ….. but let’s take a look at that in the next blog.

« | »
Have a Question?

Get in touch with our expert team and see how we can help with your IT project, call us on +44(0) 870 2411 550 or use our contact form…