Mimic pivot table using XSLT 1.0

Question

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.

The XML file's data and the table's data do not match. In this state it's impossible to answer your question; even aside the fact that you, by now, have **not shown** any own effort to solve this. — zx485, Nov 24 '18 at 22:48
Hello @zx485. Thansk for replying. The table image I included is just used to illustrate the end result I wish to accoplish. It is not supposed to match the XML data I included. Also, the XML i copied in the question is just a fraction of the actual XML file I am using. Finally, please look at the the edited question in which you will find: - The XSL file I used, which almost accomplishes the desired goal. - I included an image of the resulting HTML file after applying the XSL posted Apologies for keeping information since this app deals with patient data. — ivan quintero, Nov 25 '18 at 01:04

Martin Honnen · Accepted Answer · 2018-11-25T16:03:18.247

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

Thanks for replying Martin. That worked, however, I need a way for the template to automatically accommodate new entries for Sex (e.g. Female - Pregnant). So the key passed to Sex cannot be fixed. Is this possible? — ivan quintero, Nov 25 '18 at 14:49
See https://xsltfiddle.liberty-development.net/3NzcBue/2, I think you want respectively need to process the unique gender values each time to create a `td` and then inside map to the relevant `Record` group if it exists. If the code in the link helps I will morph that into an answer later. — Martin Honnen, Nov 25 '18 at 15:12
Martin, yes, that looks like exactly what I need. You are a life saver. I will try it as soon as I can. Thanks for helping. — ivan quintero, Nov 25 '18 at 15:33

Mimic pivot table using XSLT 1.0

1 Answers1