transform xml to xml (excel) with xslt 2.0: group by

In the context of transforming xml to Excel (as open XML) I would like to group by some categories to arrange my ouput but display category names as header. eg:

<MYROW>
    <CATEGORYNAME>first</CATEGORYNAME>
    <DATA>10</DATA>
</MYROW>
<MYROW>
    <CATEGORYNAME>second</CATEGORYNAME>
    <DATA>20</DATA>
</MYROW>
<MYROW>
    <CATEGORYNAME>second</CATEGORYNAME>
    <DATA>30</DATA>
</MYROW>
<MYROW>
    <CATEGORYNAME>first</CATEGORYNAME>
    <DATA>100</DATA>
</MYROW>

should tranform into:

<ROW>
    <CELL>
        <DATA>
            first
        </DATA>
    </CELL>
</ROW>
<ROW>
    <CELL>
        <DATA>
            10
        </DATA>
    </CELL>
</ROW>
<ROW>
    <CELL>
        <DATA>
            100
        </DATA>
    </CELL>
</ROW>
<ROW>
    <CELL>
        <DATA>
            second
        </DATA>
    </CELL>
</ROW>
<ROW>
    <CELL>
        <DATA>
            20
        </DATA>
    </CELL>
</ROW>
<ROW>
    <CELL>
        <DATA>
            30
        </DATA>
    </CELL>
</ROW>

I tried group-by, filters, variables but could not find a way to put under the same element (excel row in my case) the group by key and the associated nodes.

I am using altova mapforce.

Solution

It is a simple grouping problem:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">

    <xsl:output indent="yes"/>

    <xsl:template match="ROOT">
        <Worksheet>
            <xsl:for-each-group select="MYROW" group-by="CATEGORYNAME">
                <ROW>
                    <CELL>
                        <DATA>
                            <xsl:value-of select="current-grouping-key()"/>
                        </DATA>
                    </CELL>
                </ROW>
                <xsl:apply-templates select="current-group()"/>
            </xsl:for-each-group>
        </Worksheet>
    </xsl:template>

    <xsl:template match="MYROW">
        <ROW>
            <CELL>
                <xsl:copy-of select="DATA"/>
            </CELL>
        </ROW>
    </xsl:template>
</xsl:transform>

transforms

<?xml version="1.0" encoding="UTF-8"?>
<ROOT>
<MYROW>
    <CATEGORYNAME>first</CATEGORYNAME>
    <DATA>10</DATA>
</MYROW>
<MYROW>
    <CATEGORYNAME>second</CATEGORYNAME>
    <DATA>20</DATA>
</MYROW>
<MYROW>
    <CATEGORYNAME>second</CATEGORYNAME>
    <DATA>30</DATA>
</MYROW>
<MYROW>
    <CATEGORYNAME>first</CATEGORYNAME>
    <DATA>100</DATA>
</MYROW>
</ROOT>

into

<?xml version="1.0" encoding="UTF-8"?>
<Worksheet>
   <ROW>
      <CELL>
         <DATA>first</DATA>
      </CELL>
   </ROW>
   <ROW>
      <CELL>
         <DATA>10</DATA>
      </CELL>
   </ROW>
   <ROW>
      <CELL>
         <DATA>100</DATA>
      </CELL>
   </ROW>
   <ROW>
      <CELL>
         <DATA>second</DATA>
      </CELL>
   </ROW>
   <ROW>
      <CELL>
         <DATA>20</DATA>
      </CELL>
   </ROW>
   <ROW>
      <CELL>
         <DATA>30</DATA>
      </CELL>
   </ROW>
</Worksheet>

Online at http://xsltransform.net/pPzifp8.