Pivoting XML using XSLT

Question

Using XML in this format:

<?xml version="1.0"?>
<GetResult version="1.0">
<Fetch>
    <StartTime>2004-08-01 00:00:00</StartTime>
    <EndTime>2004-08-01 00:00:00</EndTime>
</Fetch>
<Items>
    <Item>
        <Name>Item Name Number 1</Name>
        <Data>
            <Datum>
                <Timestamp>2004-07-31 16:00:00+00:00</Timestamp>
                <Value><![CDATA[25]]></Value>
            </Datum>
            <Datum>
                <Timestamp>2004-07-31 18:00:00+00:00</Timestamp>
                <Value><![CDATA[35]]></Value>
            </Datum>
        </Data>
    </Item> 
    <Item>
        <Name>Item Number 2</Name>
        <Data>
            <Datum>
                <Timestamp>2004-07-31 16:00:00+00:00</Timestamp>
                <Value><![CDATA[45]]></Value>
            </Datum>
            <Datum>
                <Timestamp>2004-07-31 17:00:00+00:00</Timestamp>
                <Value><![CDATA[55]]></Value>
            </Datum>
            <Datum>
                <Timestamp>2004-07-31 18:00:00+00:00</Timestamp>
                <Value><![CDATA[65]]></Value>
            </Datum>
        </Data>
    </Item> 
</Items>
</GetResult>

I'd like to be able to produce a table like so, using XSLT:

<table>
  <tr>
    <th>Timestamp</th>
    <th>Item Name Number 1</th>
    <th>Item Number 2</th>
  </tr>
  <tr>
    <td>2004-07-31 16:00:00+00:00</td>
    <td>25</td>
    <td>45</td>
  </tr>
  <tr>
    <td>2004-07-31 17:00:00+00:00</td>
    <td></td>
    <td>55</td>
  </tr>
  <tr>
    <td>2004-07-31 18:00:00+00:00</td>
    <td>35</td>
    <td>65</td>
  </tr>
</table>

This would have to work regardless of how many Items are returned and how many Datums under each item. I've read some other answers which are similar without any luck. I'm fairly new to XSLT and it is driving me crazy. A solution for this would be greatly appreciated.

Answer 1

There are few steps which you have to do.

1. get list of items
2. get list of timestamps
3. for each timestamp you have to print value for each item

It may sound difficult but it is really simple if you know basic of XSLT

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
  <xsl:variable name="items" select="//Item" />
  <xsl:variable name="timestamps" select="distinct-values(//Timestamp)" />

  <xsl:template match="/" >
     <table>
       <tr>
         <th>Timestamp</th>
         <xsl:for-each select="$items">
           <th><xsl:value-of select="Name" /></th>
         </xsl:for-each>
       </tr>

       <xsl:for-each select="$timestamps">
         <xsl:variable name="stamp" select="."/>
         <tr>
           <td><xsl:value-of select="$stamp" /></td>
           <xsl:for-each select="$items">
             <td><xsl:value-of select=".//Datum[ Timestamp = $stamp ]/Value" /></td>
           </xsl:for-each>
         </tr>
       </xsl:for-each>

     </table>
  </xsl:template>
</xsl:stylesheet>

I hope that there are no mistakes. If they are then sorry for them but I think that main idea is clear and correct.

If you have some questions then feel free to ask.

Answer 2

Here is a method that makes use of the rather scary method of Muenchian Grouping, which you will probably see mentioned about if you look at other XSLT issues in StackOverflow, so it is worth knowing. In this instance Muenchian Grouping will be used to loop through distict Timestamp elements.

First, you define a key to look-up the timestamp elements

<xsl:key name="Timestamps" match="Timestamp" use="."/>

Thus, if you used this to look-up the key of '2004-07-31 16:00:00+00:00' it would contain two Timestamp, elements, but '2004-07-31 17:00:00+00:00' would only contain one.

To loop through distinct Timestamp elements, you would first loop through all Timestamp elements, like so

<xsl:for-each select="//Timestamp">

But you would then need a XSL:IF condition to check the Timestamp element is the first such occurence of that value. This is done by using the key. If the element happens to be first in the key list, then it can be processed.

<xsl:if test="generate-id(.) = generate-id(key('Timestamps',.)[1])">

generate-id is the method to use when you want to test two elements are the same. Putting it altogether gives:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
   <xsl:key name="Timestamps" match="Timestamp" use="."/>
   <xsl:template match="/">
      <table>
         <tr>
            <th>Timestamp</th>
            <!-- Output the Item headers -->
            <xsl:for-each select="//Item">
               <th>
                  <xsl:value-of select="Name"/>
               </th>
            </xsl:for-each>
         </tr>
         <!-- Loop through all Timestamps -->
         <xsl:for-each select="//Timestamp">
            <xsl:sort select="."/>
            <!-- Only process the element if it is the first occurence of this value -->
            <xsl:if test="generate-id(.) = generate-id(key('Timestamps',.)[1])">
               <xsl:variable name="Timestamp" select="."/>
               <tr>
                  <td>
                     <xsl:value-of select="."/>
                  </td>
                  <xsl:for-each select="//Item">
                     <td>
                        <!-- Output the relevant Value for the Item -->
                        <xsl:value-of select="Data/Datum[Timestamp=$Timestamp][1]/Value"/>
                     </td>
                  </xsl:for-each>
               </tr>
            </xsl:if>
         </xsl:for-each>
      </table>
   </xsl:template>
</xsl:stylesheet>