sonderzeichen Wie erstellt man eine HTML-Tabelle mit Zwischensummen unter Verwendung einer rekursiven Zeichenkettenverkettung?




sonderzeichen html utf-8 (2)

Alternative mit GROUPING SETS und richtige Reihenfolge. Die Zeichenfolgen werden mithilfe von XML PATH zusammengefügt.

Arbeits- SQL-Geige
Die oberste Abfrage in der Geige zeigt den Variableninhalt nach der Abfrage. Die untere Abfrage zeigt Ihnen nur, was GROUPING SETS erzeugt.

declare @tablebody nvarchar(max) = '';
select @tablebody = (select 
  case
  when storename is null then
   '<tr><td colspan="2">Subtotal</td>
        <td>'+cast(sum(sales) as varchar(15))+'</td></tr>'
 else
   '<tr><td>'+brand+'</td>
        <td>'+storename+'</td>
        <td>'+cast(sum(sales) as varchar(15))+'</td></tr>'
  end
from salesbystore
group by grouping sets
((brand, storename),(brand))
order by brand, case when storename is null then 1 else 0 end
for xml path (''), root('a'), type
).value('(/a)[1]','nvarchar(max)');
select @tablebody;

Die Gruppierung ist sicherlich eine Funktion von SQL Server 2008 und sollte die gesamte Verwendung von WITH ROLLUP ersetzen. In diesem speziellen Beispiel kann jedoch leicht in eine WITH ROLLUP für SQL Server 2005 gewechselt werden, wobei 3 Zeilen anstelle der 2 verwendet werden

group by brand, storename  -- group by grouping sets
with rollup                -- ((brand, storename),(brand))
having brand is not null

Ich habe die nächsten zwei Tabellen:

CREATE TABLE #SalesByStore (
    Brand VARCHAR(10),
    StoreName VARCHAR(50),
    Sales DECIMAL(10,2)
)

CREATE TABLE #SalesByBrand (
    Brand VARCHAR(10),
    TotalSales DECIMAL(10,2)
)

Ich versuche, einen HTML-Tabellenkörper mit rekursiver String-Verkettung zu erstellen, und ich muss die Verkäufe nach Filiale nach Marke anzeigen, und nach jeder Gruppe von Geschäften von einer Marke die Zwischensummen für Verkäufe für diese Marke anzeigen, wie folgt:

Ich mache es folgendermaßen:

DECLARE @tableBody NVARCHAR(MAX), @lastBrand VARCHAR(10);
SELECT @tableBody='';

SELECT
  @tableBody
    = @tableBody
      + 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[email protected]lastBrand)
                 + '</td></tr>'
            ELSE '' END
      + '<tr><td>' + SS.Brand + '</td><td>'
      + SS.StoreName + '</td><td>' + CAST(SS.Sales AS VARCHAR(15)) + '</td></tr>',
  @lastBrand = SS.Brand
FROM #SalesByStore SS
ORDER BY SS.Brand

Das Problem ist, dass die Sub-Abfrage, die mich die Untersumme nach Marke erhält immer NULL zurückgibt, weil @lastBrand Null für die Unterabfrage bleibt (siehe diese Stapelüberlauf Frage für eine Erklärung, warum dies geschieht: Warum Unterabfrage in rekursive Zeichenfolge Verkettung liefert immer NULL? ).

Können Sie mir eine andere Möglichkeit vorschlagen, die HTML-Tabelle mit Zwischensummen in SQL Server 2005 zu erstellen?

Übrigens muss ich die HTML-Tabelle in SQL Server erstellen, um es in einer DB-Mail zu senden.

BEARBEITEN: Ich habe den Fall vom Ende zum Anfang der Verkettung verschoben, weil die Zwischensummenzeile gezeichnet werden muss, bevor die neue Markengruppe beginnt. Entschuldige mich für den Fehler.


Glücklicherweise können wir in diesem Fall einfach Ihre Unterabfrage für einen Join austauschen und den Wert direkt auswählen:

DECLARE @tableBody NVARCHAR(MAX), @lastBrand VARCHAR(10), @lastTotal decimal(10,2);
SELECT @tableBody='';

SELECT
  @tableBody
    = @tableBody
      + CASE
          WHEN @lastBrand IS NOT NULL AND @lastBrand<>SS.Brand
            THEN '<tr><td colspan="2">Subtotal</td><td>'
                 + CAST(@lastTotal AS VARCHAR(15)) -- Add the last total
                 + '</td></tr>'
            ELSE '' END
      + '<tr><td>' + SS.Brand + '</td><td>'
      + SS.StoreName + '</td><td>' + CAST(SS.Sales AS VARCHAR(15)) + '</td></tr>',
  @lastBrand = SS.Brand,
  @lastTotal = SB.TotalSales -- Save the last total, too
FROM #SalesByStore SS
    join #SalesByBrand SB on SS.Brand = SB.Brand -- Join to get brand totals
ORDER BY SS.Brand

-- Finally add the last total
SELECT
  @tableBody
    = @tableBody
      + '<tr><td colspan="2">Subtotal</td><td>'
      + CAST(@lastTotal AS VARCHAR(15))
      + '</td></tr>'

Sie haben sicherlich eine clevere Methode zum Erstellen dieser HTML-Tabelle entwickelt. Beachten Sie, dass ich die Dinge ein wenig verschoben und eine Lösung gehackt habe.

Ich habe mit diesen Daten getestet:

insert into #SalesByStore select 'A', 'Store 1', 1000
insert into #SalesByStore select 'A', 'Store 2', 2000
insert into #SalesByStore select 'B', 'Store 3', 1500
insert into #SalesByStore select 'B', 'Store 4', 2100
insert into #SalesByStore select 'B', 'Store 5', 3100
insert into #SalesByBrand select 'A', 3000
insert into #SalesByBrand select 'B', 6700






html-table