Tabellen mehrspaltig im Bericht ausgeben

In einem aktuellen Projekt gab es die Anforderung Daten aus einer Tabelle mit einer Spalte im Bericht als mehrspaltige Tabelle auszugeben. Hier sind einige Ansätze dazu.

Die Datenbasis

Für unser Demo-Projekt holen wir uns eine Reihe von Postleitzahlen zu einer Stadt in Deutschland. Dazu habe ich aus dem Verzeichnis der Post alle Postleitzahlen vom Typ Postfach oder Straßen PLZ zu meiner Heimatstadt Aachen geholt. Dazu sind einige Klicks notwendig, falls man diese Postleitzahlen häufiger braucht, sollte man das Verzeichnis kaufen oder abonnieren.
Allerdings haben wir dann erst mal alle Postleitzahlen in einer Tabelle untereinander und könnten damit mehrere Seiten füllen. Damit wir diese Liste aber in einem Report seitenfüllend als mehrspaltige Tabelle ausgeben können sind einige wenige Ergänzungen notwendig.

Im Anhang finden sich alle Skripte und die Daten habe ich für diese Demo in eine Tabellenvariable gepackt.

Die Daten als mehrspaltige Tabelle ausgeben

Wir wollen also die Daten mehrspaltig ausgeben. Insgesamt sollen es 7 Spalten werden und jede Spalte soll in etwa gleichviele Elemente beinhalten. Die ersten Spalten sind alle vollständig gefüllt, bei den letzten Spalten kann das letzte Element leer sein. Die Reihenfolge der Daten läuft durch die erste Spalte und setzt sich dann in der jeweils folgenden Spalte fort. Hier schon mal das Ergebnis, was wir erzielen wollen.

Für diese Demo fülle ich die Daten in eine Tabellenvariable, damit jeder es einfacher nachvollziehen kann. Jede Spalte wird also von oben nach unten gefüllt. Dazu müssen die Daten mit NTILE in gleich große Portionen aufgeteilt werden. NTILE portioniert die Daten, wobei die letzten Gruppen die Rundungsdifferenzen auffangen. Falls also die Zahl nicht genau teilbar ist, haben die letzten Gruppen immer ein Mitglied weniger, bis diese Differenz ausgeglichen ist.
Bei meinen Vorträgen und Schulungen zum Thema Window Functions habe ich mich immer gefragt, was man außer der Bestimmung von Stichproben noch mit NTILE anfangen könnte. Hier ist mal eine sinnvolle Anwendung.

WITH Vorselektion AS
(
   SELECT p.PLZ,  NTILE(7) OVER(ORDER BY p.PLZ) AS nt_col                  -- Hier kommt die Anzahl Spalten rein
   FROM @MeineDaten p
)
SELECT PLZ, 
       nt_col, 
       ROW_NUMBER() OVER(PARTITION BY nt_col ORDER BY PLZ)  AS rn
FROM Vorselektion;

In der ersten CTE (Common Table Expression) "Vorselektion" teilen wir also unsere Daten in 7 Gruppen auf. Das entspricht der Anzahl der Spalten. Damit nun das erste Element aus jeder Gruppe auch in der ersten Zeile landet benötigen wir die zweite Window Function ROW_NUMBER. Diese ermittelt für jede Gruppe (PARTITION BY) aufsteigend nach PLZ (ORDER BY) die Zeilennummer. Die weitere Verarbeitung erledigt der Report für uns.

Der Report für die mehrspaltige Ausgabe

Wir brauchen eine Matrix im Report, die als Zeilengruppe die Spalte rn (ROW_NUMBER) bekommt und als Spaltengruppe die Spalte nt_col (NTILE für die Column).

Die eigentlichen Daten sind die Postleitzahlen (PLZ). Im Beispiel 1 sind die Überschriften noch bunt markiert, aber nachher wollen wir diese natürlich nicht mehr im Report sehen.

Im Beispiel 2 sind die Überschrift und die erste Spalte auf Hidden=True gesetzt und die Breite bzw. Höhe wurde minimiert. Damit erscheint die Tabelle komplett ohne den Ballast der beiden Hilfselemente im Report. Wie schon oben angedeutet, funktioniert das nur wirklich gut, wenn die Daten auf eine Seite passen. Andernfalls würden alle Spalten auf den Folgeseiten fortgesetzt und die Kontinuität der Daten wäre für den Betrachter nur schwer nachvollziehbar. Deshalb schauen wir uns jetzt eine modifizierte Version an, die mehrere Seiten erstellen kann, wobei die Reihenfolge der Daten immer nur über eine Seite geht und dann auf der Folgeseite fortgesetzt wird.

Die Daten als mehrspaltige Tabelle über mehrere Seiten gleichverteilt ausgeben

Für unser Beispiel nehmen wir mal an, dass auf eine Seite nur 3 Zeilen der Daten passen würden. Dann würden diese mit dem ersten Ansatz wie folgt aussehen.

Der gelbe Bereich wäre die erste Seite, der grüne die zweite und der blaue die dritte Seite. Wir möchten aber drei Seiten haben, die in sich abgeschlossen sind und alle einigermaßen gleichmäßig gefüllt sind.

Wir müssen also zuerst einmal schauen, ob denn unsere Daten auf eine Seite passen würden. Falls nicht, müssen wir ausrechnen, wie viele Seiten notwendig wären um diese gleichmäßig auszugeben. Hier verwende ich die Variable @Gesamtanzahl zuerst für die Anzahl der Zeilen und anschließend für die Anzahl der Seiten.

DECLARE @Gesamtanzahl   INT,
       @MaxElemente    DECIMAL(5,2) = 21;        -- Das ist die Anzahl der Spalten (7) mal Anzahl Zeilen (3), die auf eine Seite sollen

-- Gesamtanzahl berechnen
SELECT @Gesamtanzahl = COUNT(*) 
FROM @MeineDaten;


IF @Gesamtanzahl <= @MaxElemente
   SET @Gesamtanzahl = 1
ELSE
   BEGIN
       -- Falls es einen Überhang gibt, müssen wir diesen auf einer zusätzlichen Seite ausgeben
       -- Die Seiten werden dann alle gleichmäßig befüllt
       IF @Gesamtanzahl % @MaxElemente > 0
           SET @Gesamtanzahl = ROUND(@Gesamtanzahl / @MaxElemente, 0) + 1
       ELSE
           SET @Gesamtanzahl = ROUND(@Gesamtanzahl / @MaxElemente, 0);
   END;

Nachdem wir uns auf diese Weise einen Überblick verschafft haben, müssen wir die Menge der Daten zuerst auf die Seiten aufteilen:
NTILE(@Gesamtanzahl) OVER(ORDER BY PLZ) AS nt_page
und danach diese so partitionierten Daten noch einmal mit NTILE auf die 7 Spalten aufteilen.
NTILE(7) OVER(PARTITION BY nt_page ORDER BY p.PLZ) AS nt_col

Hier noch einmal der vollständige Code. Die CTE "Seiten" erledigt die Partitionierung auf die Seiten, die CTE "Spalten" verteilt diese Daten dann auf die 7 Spalten.

WITH Seiten AS
(
   SELECT PLZ, NTILE(@Gesamtanzahl) OVER(ORDER BY PLZ) AS nt_page
   FROM @MeineDaten
),
Spalten AS
(
   SELECT PLZ, nt_page, NTILE(7) OVER(PARTITION BY nt_page ORDER BY PLZ) AS nt_col  -- Hier kommt die Anzahl Spalten rein
   FROM Seiten 
)
SELECT PLZ, 
       nt_page, 
       nt_col, 
       ROW_NUMBER() OVER(PARTITION BY nt_page, nt_col ORDER BY PLZ) AS rn
FROM Spalten;

Schaut man sich die dazugehörige Matrix an, sieht man zwei Zeilengruppen und eine Spaltengruppe. Die erste Zeilengruppe regelt den Seitenumbruch, die zweite die Gruppierung über die gleiche Zeilen-Nummer. Die Spaltengruppe liefert uns die 7 Spalten für die Ausgabe.

Wenn man sich das Ergebnis anschaut, kann man klar die Aufteilung erkennen. Zur besseren Verständlichkeit habe ich die blau hinterlegten Überschriften so belassen. Man würde diese natürlich wieder auf unsichtbar und minimale Größe setzen um einen schöneren Bericht zu erhalten.

Die Daten als mehrspaltige Tabelle seitenfüllend ausgeben

Falls die Anforderung aber lautet, die Seiten möglichst gut zu füllen und dann den Überhang auf der letzten Seite auszugeben, müssen wir unser SQL etwas variieren. Die ersten 21 Elemente (7 Spalten und 3 Zeilen) sollen also auf der ersten Seite landen, die nächsten auf der zweiten Seite und der Rest auf der dritten Seite. Jetzt können wir die Berechnung der Seite entsprechend vereinfachen, indem wir einfach die Zeilen-Nummer durch die Anzahl der Elemente dividieren:
(ROW_NUMBER() OVER(ORDER BY PLZ)-1) / @MaxElemente + 1 AS rn_page

Hierin liegt auch schon der ganze Unterschied zu der Version vorher. Die Spalte habe ich allerdings in rn_page umbenannt, da sie jetzt über ROW_NUMBER ermittelt wird. Hier also der ganze Code dazu:

DECLARE @MaxElemente    INT = 21;             -- Das ist die Anzahl der Spalten (7) mal Anzahl Zeilen (3), die auf eine Seite sollen

WITH Seiten AS
(
   SELECT PLZ, (ROW_NUMBER() OVER(ORDER BY PLZ)-1) / @MaxElemente + 1 AS rn_page  -- Hier kommt die Anzahl Spalten rein
   FROM @MeineDaten
),
Spalten AS
(
   SELECT PLZ, rn_page, NTILE(7) OVER(PARTITION BY rn_page ORDER BY PLZ) AS nt_col  -- Hier kommt die Anzahl Spalten rein
   FROM Seiten
)
SELECT PLZ, 
       rn_page, 
       nt_col, 
       ROW_NUMBER() OVER(PARTITION BY rn_page, nt_col ORDER BY PLZ) AS rn
FROM Spalten;

Schaut man sich das Ergebnis im Bericht an, sieht man zwei gut gefüllte Seiten und eine dritte, die kaum noch Daten hat. Mit dem Seitenumbruch in der Zeilengruppe wird dies dann auch entsprechend ausgegeben.