Triton Db2 Geek

Confessions of a DB2 geek

IBM Gold Consultant Program and IBM Premier business Partner

Pivoting and Unpivoting data with pureXML

May 15th, 2015 - by

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.

Comments

  • Andy Anderson says:

    Ah, using XMLTABLE to un-pivot string data. One of my favourite pieces of SQL gymnastics. Thanks for promulgating it.

  • Walter Huth says:

    Hi Mark,
    great idea – and solution, thanks a lot!

    May I add two small variations you might like:
    a) getting rid of the xml attribute by using PATH ‘.’
    b) using the XMLTABLE feature FOR ORDINALITY
    instead of ROW_NUMBER.
    Here the (slightly different) example (DB2 for LUW):

    DECLARE GLOBAL TEMPORARY TABLE SESSION.measurements
    ( sensor CHAR(1)
    , values VARCHAR(100) )
    ON COMMIT PRESERVE ROWS ;
    INSERT INTO SESSION.measurements VALUES
    ( ‘A’, ‘182 208 246’)
    ,( ‘B’, ’81 82 83 113′)
    ,( ‘C’, ‘141’) ;

    What is stored:

    SENSOR VALUES
    —— ————-…
    A 182 208 246
    B 81 82 83 113
    C 141

    Intermediate result (here: without attribute):

    WITH
    measurements_xml (sensor, values_doc) AS
    (SELECT sensor
    , ” CONCAT
    REPLACE(values,’ ‘,”)
    CONCAT ”
    FROM SESSION.measurements )
    SELECT sensor, SUBSTR(values_doc,1,70) AS values_doc
    FROM measurements_xml ;

    SENSOR VALUES_DOC
    —— —————————————————-
    A 182208246
    B 818283113
    C 141

    Final result (here: via PATH ‘.’ and FOR ORDINALITY):

    WITH
    measurements_xml (sensor, values_doc) AS
    (SELECT sensor
    , ” CONCAT
    REPLACE(values,’ ‘,”)
    CONCAT ”
    FROM SESSION.measurements )
    SELECT sensor, value, SMALLINT(seqno) as seqno
    FROM measurements_xml
    , XMLTABLE (‘$d/root/v’
    PASSING XMLPARSE(DOCUMENT
    CAST(values_doc AS BLOB)) AS “d”
    COLUMNS
    value INTEGER PATH ‘.’
    , seqno FOR ORDINALITY ) AS X ;

    SENSOR VALUE SEQNO
    —— ———– ——
    A 182 1
    A 208 2
    A 246 3
    B 81 1
    B 82 2
    B 83 3
    B 113 4
    C 141 1

    I’m curious to see your next blog about the opposite problem!
    Thanks again –
    Walter

  • Walter Huth says:

    Hi Mark,
    after pushing “Post Comment” I got “Your comment is awaiting moderation.”. Then I noticed that the output of my “intermediate result” does not contain the XML tags anymore. In order to understand my comment, please run the intermediate query again. Thank you.
    Walter

  • Walter Huth says:

    Hi, Mark,
    I just noticed, in the intermediate query, the xml tags are cut out, too. You may send me your email address – then I can send you my comment.
    Thanks, Walter

    • Mark Mark says:

      Hi Walther
      I’ve used PATH’.’ and FOR ORDINALITY in some other pureXML queries but, I must admit, it hadn’t occurred to me to use them here. If you can send me your unedited comment, I’ll include that in my test script and see how it stacks up. The email is mark.gillis@triton.co.uk
      Thanks for getting back to me.
      Cheers
      Mark

« »

Tag Archives