group response as per month and year combination

Question

I want into write xslt to get response for the input xml. thanks in advance. I want to group output such that month year combination is not repeated for each of the employee details.

Input XML:

<resultset>
    <row>
        <column>
            <name>Month</name>
            <value>2</value>
        </column>
        <column>
            <name>Year</name>
            <value>2010</value>
        </column>
        <column>
            <name>EmpName</name>
            <value>Anu</value>
        </column>
        <column>
            <name>Age</name>
            <value>24</value>
        </column>
    </row>
    <row>
        <column>
            <name>Month</name>
            <value>2</value>
        </column>
        <column>
            <name>Year</name>
            <value>2010</value>
        </column>
        <column>
            <name>EmpName</name>
            <value>Nancy</value>
        </column>
        <column>
            <name>Age</name>
            <value>26</value>
        </column>
    </row>
    <row>
        <column>
            <name>Month</name>
            <value>3</value>
        </column>
        <column>
            <name>Year</name>
            <value>2010</value>
        </column>
        <column>
            <name>EmpName</name>
            <value>Ned</value>
        </column>
        <column>
            <name>Age</name>
            <value>25</value>
        </column>
    </row>
</resultset>

Output expected:

<Response>
    <PeriodInfo>
        <Month>2</Month>
        <Year>2010</Year>
        <EmployeeDetails>
            <Name>Anu</Name>
            <Age>24</Age>
        </EmployeeDetails>
        <EmployeeDetails>
            <Name>Nancy</Name>
            <Age>26</Age>
        </EmployeeDetails>
    </PeriodInfo>
    <PeriodInfo>
        <Month>3</Month>
        <Year>2010</Year>
        <EmployeeDetails>
            <Name>Ned</Name>
            <Age>25</Age>
        </EmployeeDetails>
    </PeriodInfo>
</Response>

score 2 · Answer 1 · edited Jan 09 '11 at 22:58

Define a key "row-by-month", which indexes rows by year and month.

Then use Muenchian grouping to select the unique year-month combinations in the input. The "row" template is applied once for each of these.

To generate the "EmployeeDetails" output, use key() to select all rows with the same year-month as the current summary row.

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

    <xsl:output method="xml" indent="yes" />

    <xsl:key name="row-by-month" match="row" 
             use="concat(column[name='Year']/value,'-',
                         column[name='Month']/value)" />

    <xsl:template match="resultset">
        <Response>
            <xsl:apply-templates 
                 select="row[generate-id(.) =
                             generate-id(key('row-by-month',
                                             concat(column
                                                      [name='Year']
                                                      /value,
                                                    '-',
                                                    column
                                                      [name='Month']
                                                      /value))[1])]"/>
        </Response>
    </xsl:template>

    <xsl:template match="row">
        <PeriodInfo>
            <Year>
                <xsl:value-of select="column[name='Year']/value"/>
            </Year>
            <Month>
                <xsl:value-of select="column[name='Month']/value"/>
            </Month>
            <xsl:apply-templates select="key('row-by-month',
                                             concat(column
                                                      [name='Year']
                                                      /value,
                                                    '-',
                                                    column
                                                      [name='Month']
                                                      /value))"
                                 mode="details"/>
        </PeriodInfo>
    </xsl:template>

    <xsl:template match="row" mode="details">
        <EmployeeeDetails>
            <Name>
                <xsl:value-of select="column[name='EmpName']/value"/>
            </Name>
            <Age>
                <xsl:value-of select="column[name='Age']/value"/>
            </Age>
        </EmployeeeDetails>
    </xsl:template>


</xsl:stylesheet>

Thanks a lot Lachlan for providing the answer it works like wonders — alisha, Jan 09 '11 at 23:35
@alisha, then you should accept Lachlan's answer by clicking the checkmark in the topleft of this answer. — Flack, Jan 10 '11 at 13:05

score 0 · Answer 2 · answered Jan 09 '11 at 17:39

Here is an essentially more readable variant of @Lachlan-Roche 's solution:

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

 <xsl:key name="kRowByDate" match="row"
  use="concat(column[name='Year']/value,
             '+',
             column[name='Month']/value)"/>

 <xsl:template match="/*">
  <Response>
    <xsl:apply-templates/>
  </Response>
 </xsl:template>

 <xsl:template match=
  "row[generate-id()
        =
         generate-id(key('kRowByDate',
                         concat(column[name='Year']/value,
                                '+',
                                column[name='Month']/value)
                         )[1]
                     )
         ]
  ">
  <xsl:variable name="vMonth" select="column[name='Month']/value"/>
  <xsl:variable name="vYear" select="column[name='Year']/value"/>
  <PeriodInfo>
    <Month><xsl:value-of select="$vMonth"/></Month>
    <Year><xsl:value-of select="$vYear"/></Year>
    <xsl:apply-templates mode="data"
      select="key('kRowByDate', concat($vYear,'+',$vMonth))"/>
  </PeriodInfo>
 </xsl:template>

 <xsl:template match="row"/>

 <xsl:template match="row" mode="data">
  <EmployeeDetails>
   <Name><xsl:value-of select="column[name='EmpName']/value"/></Name>
   <Age><xsl:value-of select="column[name='Age']/value"/></Age>
  </EmployeeDetails>
 </xsl:template>
</xsl:stylesheet>

when this transformation is applied on the provided XML document:

<resultset>
    <row>
        <column>
            <name>Month</name>
            <value>2</value>
        </column>
        <column>
            <name>Year</name>
            <value>2010</value>
        </column>
        <column>
            <name>EmpName</name>
            <value>Anu</value>
        </column>
        <column>
            <name>Age</name>
            <value>24</value>
        </column>
    </row>
    <row>
        <column>
            <name>Month</name>
            <value>2</value>
        </column>
        <column>
            <name>Year</name>
            <value>2010</value>
        </column>
        <column>
            <name>EmpName</name>
            <value>Nancy</value>
        </column>
        <column>
            <name>Age</name>
            <value>26</value>
        </column>
    </row>
    <row>
        <column>
            <name>Month</name>
            <value>3</value>
        </column>
        <column>
            <name>Year</name>
            <value>2010</value>
        </column>
        <column>
            <name>EmpName</name>
            <value>Ned</value>
        </column>
        <column>
            <name>Age</name>
            <value>25</value>
        </column>
    </row>
</resultset>

the wanted, correct result is produced:

<Response>
   <PeriodInfo>
      <Month>2</Month>
      <Year>2010</Year>
      <EmployeeDetails>
         <Name>Anu</Name>
         <Age>24</Age>
      </EmployeeDetails>
      <EmployeeDetails>
         <Name>Nancy</Name>
         <Age>26</Age>
      </EmployeeDetails>
   </PeriodInfo>
   <PeriodInfo>
      <Month>3</Month>
      <Year>2010</Year>
      <EmployeeDetails>
         <Name>Ned</Name>
         <Age>25</Age>
      </EmployeeDetails>
   </PeriodInfo>
</Response>

group response as per month and year combination

2 Answers2