xml xslt pivot-table xslt-1.0 muenchian-grouping

Mimic pivot table using XSLT 1.0

I would like a pivot table that will show the following XML.

<Records reportTime24h="18:02" reportTime="06:02:56PM" reportDate="2018-11-24" reportTitle="Pivot table">
<Record>
<Year>2017</Year>
<Month>11</Month>
<Sex>F</Sex>
<TestID>1001</TestID>
<TestName>TRIGLYCERIDEN(501)</TestName>
<Total>91</Total>
</Record>
<Record>
<Year>2017</Year>
<Month>11</Month>
<Sex>F</Sex>
<TestID>1003</TestID>
<TestName>UREUM(501)</TestName>
<Total>62</Total>
</Record>
<Record>
<Year>2017</Year>
<Month>11</Month>
<Sex>M</Sex>
<TestID>1003</TestID>
<TestName>UREUM(501)</TestName>
<Total>1642</Total>
</Record>
<Record>
<Year>2017</Year>
<Month>11</Month>
<Sex>F</Sex>
<TestID>1004</TestID>
<TestName>NATRIUM(501)</TestName>
<Total>72</Total>
</Record>
<Record>
<Year>2017</Year>
<Month>11</Month>
<Sex>M</Sex>
<TestID>1004</TestID>
<TestName>NATRIUM(501)</TestName>
<Total>1929</Total>
</Record>
<Record>
<Year>2017</Year>
<Month>11</Month>
<Sex>F</Sex>
<TestID>1005</TestID>
<TestName>KALIUM(501)</TestName>
<Total>72</Total>
</Record>
<Record>
<Year>2017</Year>
<Month>11</Month>
<Sex>M</Sex>
<TestID>1005</TestID>
<TestName>KALIUM(501)</TestName>
<Total>1929</Total>
</Record>
</Records>

Here is what the table should look like.

The cross between rows and columns should be the Total xml node that corresponds to the data intersected.

Is this possible?

PD: I have tried to do this using muenchian grouping. However, I was not able to iterate of the data across colums effectively. For example, the logic was not able to handle nodes that only had data for one of the genders. I tried to check for data in the node to display a zero (0) but failed.

EDIT

To follow the commenters recommendation.

Here is the XSL file that I worked on. It accomplishes the desired result but it fails when a Test only has data for one gender. It will place the data on the first column, regardless of wheter the datum belongs to the columns (gender).

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:key name="key-tests" match="Record" use="TestID" />
<xsl:key name="key-sex" match="Record" use="Sex" />

<xsl:key name="key-tests-sex" match="Record" use="concat(TestID,'::',Sex)" />

<xsl:template match="/Records">

<html>

    <head>

        <style>

            body        { font-family: monospace;                                       }
            table       { border-collapse: collapse; font-size: 8pt;                    }
            table thead { background-color: gainsboro; font-weight: bold;               }
            td,th       { border: 1px solid gainsboro; padding: 3px; min-width: 26px;   }
            tbody td    { text-align: right;                                    }

        </style>

    </head>

    <body>

        <table>

            <thead>
                <tr>
                    <th>Sex</th>
                    <xsl:apply-templates select="Record[generate-id() = generate-id(key('key-sex',Sex)[1])]" mode="key-sex"/>
                </tr>
            </thead>

            <tbody>
              <xsl:apply-templates select="Record[generate-id() = generate-id(key('key-tests',TestID)[1])]" mode="key-tests"/>
                <tr>
                    <th></th>
                    <th><xsl:value-of select="sum(key('key-sex','F')/Total)"/></th>
                    <th><xsl:value-of select="sum(key('key-sex','M')/Total)"/></th>
                </tr>
            </tbody>

        </table>       

    </body>

</html>

</xsl:template>
<!--Row Data (totals)-->
<xsl:template match="Record" mode="key-tests-sex">
    <td><xsl:value-of select="Total"/></td>
</xsl:template>

<!-- Doctors (HEADER ROW) -->
<xsl:template match="Record" mode="key-sex">
    <th><xsl:value-of select="Sex"/></th>
</xsl:template>

<!-- Tests (ROWS) -->
<xsl:template match="Record" mode="key-tests">
    <tr>
        <td><xsl:value-of select="TestName"/></td>
        <xsl:apply-templates select="key('key-tests',TestID)[generate-id() = generate-id(key('key-tests-sex',concat(TestID,'::',Sex))[1])]" mode="key-tests-sex"/>
    </tr>

</xsl:template>

</xsl:stylesheet>

Here is the end result image. I only included the portion visible in the view port. It is about 3 pages long. But should be enough to get the idea of what I am trying to accomplish.

What's wrong with the picture

The count shown for Female (7) is actually the count for Male.

Solution

Try to change the template matching Record for mode key-tests to

<!-- Tests (ROWS) -->
<xsl:template match="Record" mode="key-tests">
    <tr>
        <td><xsl:value-of select="TestName"/></td>
        <td>
            <xsl:value-of select="key('key-tests',TestID)[generate-id() = generate-id(key('key-tests-sex',concat(TestID,'::','F'))[1])]/Total"/>
        </td>
        <td>
            <xsl:value-of select="key('key-tests',TestID)[generate-id() = generate-id(key('key-tests-sex',concat(TestID,'::','M'))[1])]/Total"/>
        </td>
    </tr>
</xsl:template>

That at least should fix the problem with the wrong relation of the number and the gender.

As in your real data the different values of the Sex element are not restricted to F and M you will need some way to process the unique values each time to create a td cell and inside then map to the relevant Record data so the template will become

<!-- Tests (ROWS) -->
<xsl:template match="Record" mode="key-tests">
    <tr>
        <td><xsl:value-of select="TestName"/></td>
        <xsl:variable name="testId" select="TestID"/>
        <xsl:for-each select="$unique-genders">
            <td>
              <xsl:value-of select="key('key-tests', $testId)[generate-id() = generate-id(key('key-tests-sex',concat(TestID, '::', current()))[1])]/Total"/>  
            </td>
        </xsl:for-each>
    </tr>
</xsl:template>

with declarations

<xsl:key name="key-sex" match="Sex" use="." />

<xsl:variable name="unique-genders" select="//Sex[generate-id() = generate-id(key('key-sex', .)[1])]"/>

as done in https://xsltfiddle.liberty-development.net/3NzcBue/2