Hi all,

Hoping for some help! Im using the jesper SQL method to retrieve data from my DB, everything is pulling through absolutely fine however it's the display im having trouble with.

Ive pasted my code below, basicaly i need all the 'for-each' data in a single row, what is happening is for each table i am querying it's creating a new row, in this case i am pulling related data from 3 tables so it is creating three rows with the data split between in row (according to table from).

Anyone have any ideas how i can get it to return a single row? the same query run straight to SQL returns the results back perfectly.

Many thanks

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xsl:stylesheet [ <!ENTITY nbsp "&#x00A0;"> ]>
<xsl:stylesheet
  version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:msxml="urn:schemas-microsoft-com:xslt"
  xmlns:umbraco.library="urn:umbraco.library" xmlns:Exslt.ExsltCommon="urn:Exslt.ExsltCommon" xmlns:Exslt.ExsltDatesAndTimes="urn:Exslt.ExsltDatesAndTimes" xmlns:Exslt.ExsltMath="urn:Exslt.ExsltMath" xmlns:Exslt.ExsltRegularExpressions="urn:Exslt.ExsltRegularExpressions" xmlns:Exslt.ExsltStrings="urn:Exslt.ExsltStrings" xmlns:Exslt.ExsltSets="urn:Exslt.ExsltSets" xmlns:jesper.sql="urn:jesper.sql" xmlns:netop.dofo="urn:netop.dofo" xmlns:CommerceLibrary="urn:CommerceLibrary"
  exclude-result-prefixes="msxml umbraco.library Exslt.ExsltCommon Exslt.ExsltDatesAndTimes Exslt.ExsltMath Exslt.ExsltRegularExpressions Exslt.ExsltStrings Exslt.ExsltSets jesper.sql ">


<xsl:output method="xml" omit-xml-declaration="yes"/>

<xsl:param name="currentPage"/>
    
    <xsl:template match="/">

  <xsl:variable name="sqlexp">
SELECT     dbo.Property.PropertyReference, dbo.Contact.FullName, dbo.Booking.BookingID, dbo.Booking.StartDate, dbo.Booking.EndDate, dbo.Booking.HolidayNotes, dbo.Booking.InternalNotes

FROM         dbo.Booking INNER JOIN
                      dbo.Contact ON dbo.Booking.ContactID = dbo.Contact.ContactID INNER JOIN
                      dbo.Property ON dbo.Booking.PropertyID = dbo.Property.PropertyID
WHERE 1 = (FLOOR(DATEDIFF(dd,startDate,GETDATE()+7) / 365.25))
          -
          (FLOOR(DATEDIFF(dd,startDate,GETDATE()-2) / 365.25))

 
</xsl:variable>

<xsl:variable name="sqlexptwo">
SELECT     dbo.Property.PropertyReference, dbo.Contact.FullName, dbo.Booking.BookingID, dbo.Booking.StartDate, dbo.Booking.EndDate, dbo.Booking.HolidayNotes, dbo.Booking.InternalNotes

FROM         dbo.Booking INNER JOIN
                      dbo.Contact ON dbo.Booking.ContactID = dbo.Contact.ContactID INNER JOIN
                      dbo.Property ON dbo.Booking.PropertyID = dbo.Property.PropertyID
WHERE 1 = (FLOOR(DATEDIFF(dd,startDate,GETDATE()+14) / 365.25))
          -
          (FLOOR(DATEDIFF(dd,startDate,GETDATE()-2) / 365.25))

 
</xsl:variable>      
      
<xsl:variable name="sqlresult" select="jesper.sql:SQLXml($sqlexp)"/>
<xsl:variable name="sqlresulttwo" select="jesper.sql:SQLXml($sqlexptwo)"/>
      
<style>
 .sqltable{
  background:EEEEF6;
  }
.sqltable tr{
  background:yellow;
  text-align:center;
  }
.sqltable tr-alt{
  background:blue;
  text-align:center;
  }

</style>
    
<div class="sqltable">   
  <h3>Arriving within next 7 days</h3>  <br />
<table border="2" align="center" style="empty-cells : hide; background:#EEEEF6;">
<tr style="background:white; cell-padding:15px;">
      <td>Prop Ref</td>
      <td>Full Name</td>
      <td>BookingID</td>
      <td style="width:10px; overflow:hidden;">Start Date</td>
      <td>End Date</td>
      <td>Holiday Notes</td>
      <td>Internal Notes</td>
    </tr>

<xsl:for-each select="$sqlresult//.">
 <tr>
          <td><xsl:value-of select="@PropertyReference"/></td>
          <td><xsl:value-of select="@FullName"/></td>
      <td><xsl:value-of select="@BookingID"/></td>
      <td style="width:120px;"><xsl:value-of select="@StartDate"/></td>
      <td style="width:120px;"><xsl:value-of select="@EndDate"/></td>
      <td style="width:220px;"><xsl:value-of select="@HolidayNotes"/></td>
      <td style="width:220px;"><xsl:value-of select="@InternalNotes"/></td>
       </tr>
  </xsl:for-each>

  </table>
      </div>
      
      <div class="sqltable">
        <h3>Arriving within next 14 days</h3>  
<table border="2" align="center" style="empty-cells : hide; margin-top:20px; background:#DADADA;">
<tr style="background:white; cell-padding:15px;">
      <td>Prop Ref</td>
      <td>Full Name</td>
      <td>BookingID</td>
      <td style="width:10px; overflow:hidden;">Start Date</td>
      <td>End Date</td>
      <td>Holiday Notes</td>
      <td>Internal Notes</td>
    </tr>

<xsl:for-each select="$sqlresulttwo//.">
 <tr>
          <td><xsl:value-of select="@PropertyReference"/></td>
          <td><xsl:value-of select="@FullName"/></td>
      <td><xsl:value-of select="@BookingID"/></td>
      <td style="width:120px;"><xsl:value-of select="@StartDate"/></td>
      <td style="width:120px;"><xsl:value-of select="@EndDate"/></td>
      <td style="width:220px;"><xsl:value-of select="@HolidayNotes"/></td>
      <td style="width:220px;"><xsl:value-of select="@InternalNotes"/></td>
       </tr>
  </xsl:for-each>

  </table>
      </div>

</xsl:template>

</xsl:stylesheet>