XSLT Pivot Table with Blanks - 【StackMirror】|xml|xslt|xslt-1.0

So I have an xml document that does not contain the full cross product in the final table but I need the xslt to put the blanks in for me. If you run this xslt you can see that GCM should be under ABC. I know that I must need to count result and then add a blank but I am stuck.

<root>
  <Cell>
    <place>BRM</place>
    <test>DMC</test>
    <Score>70</Score>
    <Colour>#008000</Colour>
    <GenerateDate>2012-04-04 12:46:41</GenerateDate>
    <testgroup> Process Plant</testgroup>
  </Cell>
  <Cell>
    <place>GCM</place>
    <test>DMC</test>
    <Score>76</Score>
    <Colour>#008000</Colour>
    <GenerateDate>2012-04-04 12:46:41</GenerateDate>
    <testgroup> Process Plant</testgroup>
  </Cell>
  <Cell>
    <place>GRM</place>
    <test>DMC</test>
    <Score>72</Score>
    <Colour>#008000</Colour>
    <GenerateDate>2012-04-04 12:46:41</GenerateDate>
    <testgroup> Process Plant</testgroup>
  </Cell>
  <Cell>
    <place>NPM</place>
    <test>DMC</test>
    <Score>80</Score>
    <Colour>#008000</Colour>
    <GenerateDate>2012-04-04 12:46:41</GenerateDate>
    <testgroup> Process Plant</testgroup>
  </Cell>
  <Cell>
    <place>PDM</place>
    <test>DMC</test>
    <Score>88</Score>
    <Colour>#008000</Colour>
    <GenerateDate>2012-04-04 12:46:41</GenerateDate>
    <testgroup> Process Plant</testgroup>
  </Cell>
  <Cell>
    <place>PTM</place>
    <test>DMC</test>
    <Score>58</Score>
    <Colour>#FFA100</Colour>
    <GenerateDate>2012-04-04 12:46:41</GenerateDate>
    <testgroup> Process Plant</testgroup>
  </Cell>
  <Cell>
    <place>SRM</place>
    <test>DMC</test>
    <Score>62</Score>
    <Colour>#FFA100</Colour>
    <GenerateDate>2012-04-04 12:46:41</GenerateDate>
    <testgroup> Process Plant</testgroup>
  </Cell>
  <Cell>
    <place>SWC</place>
    <test>DMC</test>
    <Score>85</Score>
    <Colour>#008000</Colour>
    <GenerateDate>2012-04-04 12:46:41</GenerateDate>
    <testgroup> Process Plant</testgroup>
  </Cell>
  <Cell>
    <place>RVS</place>
    <test>DMC</test>
    <Score>84</Score>
    <Colour>#008000</Colour>
    <GenerateDate>2012-04-04 12:46:41</GenerateDate>
    <testgroup> Process Plant</testgroup>
  </Cell>
  <Cell>
    <place>BWM</place>
    <test>SUR</test>
    <Score>66</Score>
    <Colour>#FFA100</Colour>
    <GenerateDate>2012-04-04 12:46:41</GenerateDate>
    <testgroup>Survey</testgroup>
  </Cell>
  <Cell>
    <place>PDM</place>
    <test>SUR</test>
    <Score>85</Score>
    <Colour>#008000</Colour>
    <GenerateDate>2012-04-04 12:46:41</GenerateDate>
    <testgroup>Survey</testgroup>
  </Cell>
  <Cell>
    <place>SRM</place>
    <test>SUR</test>
    <Score>41</Score>
    <Colour>#FFA100</Colour>
    <GenerateDate>2012-04-04 12:46:41</GenerateDate>
    <testgroup>Survey</testgroup>
  </Cell>
</root>

XSLT

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
  <xsl:output method="html" indent="yes"/>

  <xsl:key name="muench" match="/root/Cell/place" use="."/>

  <xsl:key name="test-key" match="/root/Cell/test" use="."/>

  <xsl:template match="/">
    <html>
      <head>
        <title>ABC test Report</title>
        <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
      </head>
      <body >
        <div >
          <header>
            <h2 >
              test group - <xsl:value-of select="/root/Cell/testgroup"/>
            </h2>
          </header>
          <div class="content">
            <table >
              <thead>
                <tr>
                  <th >Place</th>
                  <xsl:for-each select="/root/Cell/test[generate-id() = generate-id(key('test-key',.)[1])]">
                    <th >
                      <xsl:value-of select="."/>
                    </th>
                  </xsl:for-each>
                </tr>
              </thead>
              <tbody>
                <xsl:for-each select="/root/Cell/place[generate-id() = generate-id(key('muench',.)[1])]">
                  <xsl:call-template name="pivot">
                    <xsl:with-param name="place" select="."/>
                  </xsl:call-template>
                </xsl:for-each>
              </tbody>
            </table>
          </div>
          <footer >
            <p></p>
          </footer>
        </div>
      </body>
    </html>
  </xsl:template>

  <xsl:template name="pivot">
    <xsl:param name="place"/>
    <tr>
      <td >
        <xsl:value-of select="$place"/>
      </td>
      <xsl:for-each select="/root/Cell/test[generate-id() = generate-id(key('test-key',.)[1])]">
      <xsl:for-each select="/root/Cell[place=$place and test=.]">
        <xsl:choose>
            <xsl:when test="count(.)=1">
                <td>
                  <xsl:attribute name="style">
                    background-color:<xsl:value-of select="Colour"/>;
                  </xsl:attribute>
                  <xsl:value-of select="Score"/>%
                </td>
            </xsl:when>
            <xsl:otherwise>
                <td>
                </td>
            </xsl:otherwise>
        </xsl:choose>
      </xsl:for-each>
      </xsl:for-each>
    </tr>
  </xsl:template>
</xsl:stylesheet>

output I wanted

<html>
  <table border="1">
    <thead>
      <th>Place</th>
      <th>DMC</th>
      <th>SUR</th>
    </thead>
    <tbody>
      <tr>
        <td>BRM</td>
        <td> 70</td>
        <td> </td>
      </tr>
      <tr>
        <td>BWM</td>
        <td> </td>
        <td> 66</td>
      </tr>
      <tr>
        <td>GCM</td>
        <td> 76</td>
        <td> </td>
      </tr>
      <tr>
        <td>GRM</td>
        <td> 72</td>
        <td> </td>
      </tr>
      <tr>
        <td>NPM</td>
        <td> 80</td>
        <td> </td>
      </tr>
      <tr>
        <td>PDM</td>
        <td> 88</td>
        <td> 85</td>
      </tr>
      <tr>
        <td>PTM</td>
        <td> 58</td>
        <td> </td>
      </tr>
      <tr>
        <td>RVS</td>
        <td> 84</td>
        <td> </td>
      </tr>
      <tr>
        <td>SRM</td>
        <td> 62</td>
        <td> 41</td>
      </tr>
      <tr>
        <td>SWC</td>
        <td> 85</td>
        <td> </td>
      </tr>
    </tbody>
  </table>
</html>

2012-04-04 00:51
by jimconstable

So, what is the exact wanted result? Please, provide. Also, please, explain the rules that the transformation must implement - Dimitre Novatchev 2012-04-04 03:20

This shorter and simpler transformation:

<xsl:stylesheet version="1.0"
     xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
     <xsl:output omit-xml-declaration="yes" indent="yes"/>

     <xsl:key name="kTests" match="test" use="."/>
     <xsl:key name="kCellByPlace" match="Cell" use="place"/>

     <xsl:variable name="vTests" select=
      "/*/*/test
             [generate-id()
             =
              generate-id(key('kTests', .)[1])
             ]
      "/>

     <xsl:template match="/*">
      <html>
          <table border="1">
            <thead>
              <th>Place</th>
              <xsl:apply-templates select="$vTests"/>
            </thead>
            <tbody>
              <xsl:apply-templates select=
               "Cell[generate-id() = generate-id(key('kCellByPlace', place))]">
                <xsl:sort select="place"/>
              </xsl:apply-templates>
            </tbody>
          </table>
      </html>
     </xsl:template>

     <xsl:template match="test">
      <th><xsl:value-of select="."/></th>
     </xsl:template>

     <xsl:template match="Cell">
      <tr>
       <td><xsl:value-of select="place"/></td>
        <xsl:apply-templates select="$vTests" mode="row">
         <xsl:with-param name="pCells" select="key('kCellByPlace', place)"/>
        </xsl:apply-templates>
      </tr>
     </xsl:template>

     <xsl:template match="test" mode="row">
      <xsl:param name="pCells"/>

      <td>
        <xsl:value-of select=
         "concat('&#xA0;',$pCells[test=current()]/Score)"/>
      </td>
     </xsl:template>
</xsl:stylesheet>

when applied on the provided XML document:

<root>
    <Cell>
        <place>BRM</place>
        <test>DMC</test>
        <Score>70</Score>
        <Colour>#008000</Colour>
        <GenerateDate>2012-04-04 12:46:41</GenerateDate>
        <testgroup> Process Plant</testgroup>
    </Cell>
    <Cell>
        <place>GCM</place>
        <test>DMC</test>
        <Score>76</Score>
        <Colour>#008000</Colour>
        <GenerateDate>2012-04-04 12:46:41</GenerateDate>
        <testgroup> Process Plant</testgroup>
    </Cell>
    <Cell>
        <place>GRM</place>
        <test>DMC</test>
        <Score>72</Score>
        <Colour>#008000</Colour>
        <GenerateDate>2012-04-04 12:46:41</GenerateDate>
        <testgroup> Process Plant</testgroup>
    </Cell>
    <Cell>
        <place>NPM</place>
        <test>DMC</test>
        <Score>80</Score>
        <Colour>#008000</Colour>
        <GenerateDate>2012-04-04 12:46:41</GenerateDate>
        <testgroup> Process Plant</testgroup>
    </Cell>
    <Cell>
        <place>PDM</place>
        <test>DMC</test>
        <Score>88</Score>
        <Colour>#008000</Colour>
        <GenerateDate>2012-04-04 12:46:41</GenerateDate>
        <testgroup> Process Plant</testgroup>
    </Cell>
    <Cell>
        <place>PTM</place>
        <test>DMC</test>
        <Score>58</Score>
        <Colour>#FFA100</Colour>
        <GenerateDate>2012-04-04 12:46:41</GenerateDate>
        <testgroup> Process Plant</testgroup>
    </Cell>
    <Cell>
        <place>SRM</place>
        <test>DMC</test>
        <Score>62</Score>
        <Colour>#FFA100</Colour>
        <GenerateDate>2012-04-04 12:46:41</GenerateDate>
        <testgroup> Process Plant</testgroup>
    </Cell>
    <Cell>
        <place>SWC</place>
        <test>DMC</test>
        <Score>85</Score>
        <Colour>#008000</Colour>
        <GenerateDate>2012-04-04 12:46:41</GenerateDate>
        <testgroup> Process Plant</testgroup>
    </Cell>
    <Cell>
        <place>RVS</place>
        <test>DMC</test>
        <Score>84</Score>
        <Colour>#008000</Colour>
        <GenerateDate>2012-04-04 12:46:41</GenerateDate>
        <testgroup> Process Plant</testgroup>
    </Cell>
    <Cell>
        <place>BWM</place>
        <test>SUR</test>
        <Score>66</Score>
        <Colour>#FFA100</Colour>
        <GenerateDate>2012-04-04 12:46:41</GenerateDate>
        <testgroup>Survey</testgroup>
    </Cell>
    <Cell>
        <place>PDM</place>
        <test>SUR</test>
        <Score>85</Score>
        <Colour>#008000</Colour>
        <GenerateDate>2012-04-04 12:46:41</GenerateDate>
        <testgroup>Survey</testgroup>
    </Cell>
    <Cell>
        <place>SRM</place>
        <test>SUR</test>
        <Score>41</Score>
        <Colour>#FFA100</Colour>
        <GenerateDate>2012-04-04 12:46:41</GenerateDate>
        <testgroup>Survey</testgroup>
    </Cell>
</root>

produces the wanted result :

<html>
   <table border="1">
      <thead>
         <th>Place</th>
         <th>DMC</th>
         <th>SUR</th>
      </thead>
      <tbody>
         <tr>
            <td>BRM</td>
            <td>&nbsp;70</td>
            <td>&nbsp;</td>
         </tr>
         <tr>
            <td>BWM</td>
            <td>&nbsp;</td>
            <td>&nbsp;66</td>
         </tr>
         <tr>
            <td>GCM</td>
            <td>&nbsp;76</td>
            <td>&nbsp;</td>
         </tr>
         <tr>
            <td>GRM</td>
            <td>&nbsp;72</td>
            <td>&nbsp;</td>
         </tr>
         <tr>
            <td>NPM</td>
            <td>&nbsp;80</td>
            <td>&nbsp;</td>
         </tr>
         <tr>
            <td>PDM</td>
            <td>&nbsp;88</td>
            <td>&nbsp;85</td>
         </tr>
         <tr>
            <td>PTM</td>
            <td>&nbsp;58</td>
            <td>&nbsp;</td>
         </tr>
         <tr>
            <td>RVS</td>
            <td>&nbsp;84</td>
            <td>&nbsp;</td>
         </tr>
         <tr>
            <td>SRM</td>
            <td>&nbsp;62</td>
            <td>&nbsp;41</td>
         </tr>
         <tr>
            <td>SWC</td>
            <td>&nbsp;85</td>
            <td>&nbsp;</td>
         </tr>
      </tbody>
   </table>
</html>

Explanation:

We create an auxiliary variable $vTests that contains all distinct values for a test element.
Whenever we want to generate a tr that contains the score for a particular test at particular place, we generate "empty" tds for all test elements contained in $vTests, whose string value isn't equal to the string value of the specific test element, and we generate the string value of the Score sibling of the particular test element exactly when the string value of this test element is equal to the current test element from $vTests.

2012-04-04 03:47
by Dimitre Novatchev

This is close but if a place has more than one test score. It writes a new line rather than GCM4372jimconstable 2012-04-05 03:39

@jimconstable: No one can guess -- this is a solution that works with the currently provided XML document. As you haven't given any other examples and haven't defined the possible format of the source XML document, this is a complete solution of the question as currently asked. Please, edit the question and specify precisely the format of the XML document. Please, also specify what should be done in the possible different cases. This answer presents a complete solution of the question. Please, improve your way of asking questions and don't blame the people for not being able to read your mind - Dimitre Novatchev 2012-04-05 04:05

@jimconstable: Now that you have stated the problem unambiguously, I have updated my answer and it produces the wanted result. It is also shorter than your own answer, so this may deserve your attention - Dimitre Novatchev 2012-04-05 12:23

Thanks to Dimetre for the help. He showed how to use templates more effectively and created the variable. I then used this to edit his script to give the output I was looking for.

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output omit-xml-declaration="yes" indent="yes"/>

 <xsl:key name="kTests" match="test" use="."/>

 <xsl:variable name="vTests" select=
  "/*/*/test
         [generate-id()
         =
          generate-id(key('kTests', .)[1])
         ]
  "/>

  <xsl:key name="kPlaces" match="place" use="."/>

 <xsl:variable name="vPlaces" select=
  "/*/*/place
         [generate-id()
         =
          generate-id(key('kPlaces', .)[1])
         ]
  "/>

 <xsl:template match="/*">
  <html>
      <table border="1">
        <thead>
          <th>Place</th>
          <xsl:apply-templates select="$vTests">
           <xsl:sort select="." />
          </xsl:apply-templates>
        </thead>
        <tbody>
          <xsl:apply-templates select="$vPlaces">
           <xsl:sort select="." />
          </xsl:apply-templates>
        </tbody>
      </table>
  </html>
 </xsl:template>

 <xsl:template match="test">
  <th><xsl:value-of select="."/></th>
 </xsl:template>

 <xsl:template match="place">
  <tr>
   <td><xsl:value-of select="."/></td>
    <xsl:apply-templates select="$vTests" mode="row">
      <xsl:sort select="." />
     <xsl:with-param name="pPlace" select="."/>
    </xsl:apply-templates>
  </tr>
 </xsl:template>

 <xsl:template match="test" mode="row">
  <xsl:param name="pPlace"/>
  <td>
    <xsl:value-of select=
     "concat('&#xA0;',/root/Cell[test=current() and place=$pPlace]/Score)"/>
  </td>
 </xsl:template>

</xsl:stylesheet>

2012-04-05 05:41
by jimconstable