Muenchian grouping difficulties

Question

I am building a Sharepoint search results page to find the answers to questions asked in a 3rd party tool. I have a bdc connection to their sql db. I am crawling just one one table in the db. I need to group question and answer results together using the muenchian method using several columns in the same table. I cant change the db so I have to pull the results together in search to be something meaningful. This table contains all the entries of questions asked and answers given. I have to tie them together for us using search as the application to present the Q&A

overview of the data: Each record returned in the core search results has an: title - this is the question or the answer activityid(int) - unique id of the entry activitytypeid(int) - these identify if its a Q or A / 79 or 82 parentid(int) - this value contains the activityid of the original question - if the record is the original question, it will have a value of 0 for this column isanswer(boolean) true or nothing (this is sort of redundant and does the same as activitytypeid but I was not sure if I could use it to help with the grouping)

Review activitype id is either: 79 - question being asked - this will have a parentid of 0 or 82 - answer to question - this will have a parent id matching the activityid of the original question

I am trying but without much success to get my search results grouped/sorted to have the question returned first. In the example below it would be activityid 19142 and the answers to the question indented underneath with all grouped in a set

any help in reviewing my hack at this or examples would be great.

I am just trying to understand xsl and this has been a big learning curve. I have a long way to go and most certainly have jumped into the deep end on this.

raw xml of results i currently get without formatting the results

    <All_Results>
      <Result>
        <id>1</id>
        <title>Original Question</title>
        <isanswer></isanswer>
        <parentid>0</parentid>
        <activitytype>79</activitytype>
        <activityid>19142</activityid>
      </Result>
      <Result>
       <id>2</id>
       <title>Answer to original question</title>
       <isanswer>true</isanswer>
       <parentid>19142</parentid>
       <activitytype>82</activitytype>
       <activityid>19146</activityid>
     </Result>
     <Result>
       <id>3</id>
       <title>Another Question</title>
       <isanswer></isanswer>
       <parentid>0</parentid>
       <activitytype>79</activitytype>
       <activityid>19200</activityid>
     <Result>
     <Result>
       <id>4</id>
       <title>Second answer to original question</title>
       <isanswer>true</isanswer>
       <parentid>19142</parentid>
       <activitytype>82</activitytype>
       <activityid>19199</activityid>
     </Result>
     <Result>
       <id>5</id>
       <title>and another Question</title>
       <isanswer></isanswer>
       <parentid>0</parentid>
       <activitytype>79</activitytype>
       <activityid>19254</activityid>
     <Result>
     <Result>
       <id>6</id>
       <title>Answer to another question</title>
       <isanswer>true</isanswer>
       <parentid>19200</parentid>
       <activitytype>82</activitytype>
       <activityid>19265</activityid>
     </Result>
    </All_Results>

The output I am looking for would have all answers grouped with the corresponding question this is very simplified but I think it get s the point I am trying to get too.

    <All_Results>
     <Result>
       <title>Original Question</title>
       <title>Answer to original question</title>
       <title>Second answer to original question</title>
     </Result>
     <Result>
       <title>Another Question</title>
       <title>Answer to another question</title>
     </Result>
    </All_Results>

my attempts at the template

    <xsl:template match="All_Results">
     <xsl:for-each select="Result[count(. | key('QAsked', 'activityid')[1]) > 0]">
        <xsl:sort select="questionasked" />
        <xsl:value-of select="title" /><br />
        <xsl:value-of select="questionasked" /><br />
        <xsl:value-of select="activityid" /><br /><br />    
        <xsl:for-each select="key('QAsked', '82')">
            <xsl:sort select="questionasked" />
            <xsl:if test="normalize-space(questionasked) = '82'"></xsl:if><br />
            <xsl:value-of select="questionasked" /><br />
            <xsl:value-of select="originalquestion" /><br />
            <xsl:value-of select="likes" /><br /><br />
       </xsl:for-each>
     </xsl:for-each>
    </xsl:template>  

Answer 1

Try this template:

    <xsl:key name="QAsked" match="Result[(activitytype = '82') or (activitytype = '79')]" use="concat(substring(parentid, 1, number(not(parentid = '0')) * string-length(parentid)),substring(activityid, 1, number(parentid = '0') * string-length(activityid)))"/>
  <xsl:template match="All_Results">
    <All_Results>
      <xsl:for-each select="Result[count(. | key('QAsked', concat(substring(parentid, 1, number(not(parentid = '0')) * string-length(parentid)),substring(activityid, 1, number(parentid = '0') * string-length(activityid))))[1]) = 1]">
        <xsl:sort select="id" />

        <Result>
          <xsl:for-each select="key('QAsked', concat(substring(parentid, 1,  number(not(parentid = '0')) * string-length(parentid)),substring(activityid, 1,  number(parentid = '0') * string-length(activityid))))">
            <xsl:sort select="parentid" />
            <title>
              <xsl:value-of select="title" />
            </title>
          </xsl:for-each>
        </Result>
      </xsl:for-each>
    </All_Results>
  </xsl:template>

The output is xml structure which you provided as an example of the desired result.