I have the next two tables:\[code\]CREATE TABLE #SalesByStore ( Brand VARCHAR(10), StoreName VARCHAR(50), Sales DECIMAL(10,2))CREATE TABLE #SalesByBrand ( Brand VARCHAR(10), TotalSales DECIMAL(10,2))\[/code\]I am trying to build an HTML table body using recursive string concatenation, and I need to show the sales by store ordered by brand, and after each group of stores from a same brand show the sales subtotals for that brand, like this:
I am doing it the following way:\[code\]DECLARE @tableBody NVARCHAR(MAX), @lastBrand VARCHAR(10);SELECT @tableBody='';SELECT @tableBody = @tableBody + '<tr><td>' + SS.Brand + '</td><td>' + SS.StoreName + '</td><td>' + CAST(SS.Sales AS VARCHAR(15)) + '</td></tr>' + CASE WHEN @lastBrand IS NOT NULL AND @lastBrand<>SS.Brand THEN '<tr><td colspan="2">Subtotal</td><td>' + (SELECT TOP 1 CAST(SB.TotalSales AS VARCHAR(15)) FROM #SalesByBrand SB WHERE SB.Brand=@lastBrand) + '</td></tr>' ELSE '' END, @lastBrand = SS.BrandFROM #SalesByStore SSORDER BY SS.Brand\[/code\]The problem is that the sub-query that gets me the sub-total amount by brand always returns NULL because @lastBrand remains null for the sub-query (see this stack overflow question for an explain about why this happens: Why subquery inside recursive string concatenation always returns NULL?).Can you suggest me another way to create the HTML table with subtotals in SQL Server 2005?By the way, I need to build the HTML table in SQL Server in order to send it inside a db mail.