Ausgabe der Abfrage formatieren - neue Spalten

[vodanet]

Hallo zusammen,

Ich habe zwei Tabellen - eine reinigung und eine gruppe
In der Tabelle reinigung sind die verantwortliche Gruppe (id), der Typ (Typ der Reinigung) und das Datum hinterlegt.
In der Tabelle gruppe sind Details zur Gruppe (id, Name, etc.) hinterlegt.
An einem Datum können mehrere Typen von Reinigung aber nur eine Gruppe fällig sein.
Siehe Beispiel hier -> id 1 und 2

id	gruppe	typ	datum
1	5	wr	2022-05-02 06:00:00
2		zr	2022-05-02 06:00:00
3	66	ra	2022-05-21 06:00:00
4	2	wr	2022-06-05 06:00:00

Als einfache Abfrage verwende ich im Moment diese:
SELECT datum, reinigung, gruppe.name FROM `plan_reinigung` LEFT JOIN gruppe ON plan_reinigung.gruppe=gruppe.id WHERE `datum` > DATE(NOW()) ORDER by datum
Gibt es eine (elegante) Lösung (ohne die Tabelle Reinigung neu zu "schreiben"), mit der eine Ausgabe so aussehen könnte? :

id	gruppe	typ wr	typ zr	typ ra	datum
1	3	X		X	2022-05-02 06:00:00
2	55		X	X	2022-05-21 06:00:00

Also pro Datum eine Zeile und die Typen Spalten ergänzt je nachdem, ob der Typ in der Woche fällig wäre...
Wäre es ein Ansatz mehrere Joins zu verwenden und dann mit distinct datum zu arbeiten?

Letzten Endes möchte ich die Daten zeilenweise auslesen und in eine PDF schreiben.

Vielen Dank für eure Hilfe und einen schönen Tag euch!
Daniel

 

[Georg V.]

Hallo Daniel,
ich gehe mal von mehreren Tippfehlern in der zweiten Tabelle (5 statt 3, 66 statt 55) aus. Auch in der ersten Tabelle sollte für die id=2 die Gruppe gefüllt sein. Nett wäre auch, wenndie in der Beschreibung genannten Tabellen auch im SQL Statement verwendet werden. Du hast auch Deine DBMS nicht genannt, hier nehme ich mal MySQL an.

Unter allen diesen Annahmen (und einer festen Anzahl der Reinigungstypen, kannst Du das Statement als normales SELECT ... FROM ... GROUP BY gestalten, wenn Du jeden Reinigungstyp ein IF(ISNULL(gruppe), '', 'X') as "typ .." hinzufügst.

 

[dabadepdu]

Das wäre mal ein (seltener) passender und klassischer Fall für echte Pivot Abfragen.
Besonders wenn die Typen nur alle paar Jahre mal erweitert werden.
Auch das Ziel, nämlich die Datendarstellung lediglich für einen Report zu verwenden, passt gut.

Für Maria und MySQL, die Pivot beide nicht standardmäßig beherrschen, gibt es (wie in jeder DB) den Workaround den @Georg V. schon beschrieben hat.
Für Maria gibt es noch eine extra Storage Engine (vgl. Wieviel Jahre Rückstand zwischen verschiedenen DB Systemen?), die Pivot ermöglicht.

Wirklich klassisch ist für Pivotabfragen eigentlich eher die programmatische Nutzung (interaktiv für den Nutzer) oder die Erzeugung/Darstellung mittels Report Engine.

Für dynamische Erstellung von Pivot Abfragen kann natürlich auch eigener Code (Client) zur Konstruktion des Statement verwendet werden oder auch Stored Procedures bzw. speziell bei diesen Systemen hier SQL, das Variablen nutzt.

 

[vodanet]

Hallo Daniel,
ich gehe mal von mehreren Tippfehlern in der zweiten Tabelle (5 statt 3, 66 statt 55) aus. Auch in der ersten Tabelle sollte für die id=2 die Gruppe gefüllt sein. Nett wäre auch, wenndie in der Beschreibung genannten Tabellen auch im SQL Statement verwendet werden. Du hast auch Deine DBMS nicht genannt, hier nehme ich mal MySQL an.

Unter allen diesen Annahmen (und einer festen Anzahl der Reinigungstypen, kannst Du das Statement als normales SELECT ... FROM ... GROUP BY gestalten, wenn Du jeden Reinigungstyp ein IF(ISNULL(gruppe), '', 'X') as "typ .." hinzufügst.

Hallo Georg,
vielen Dank (auch dir dabadepdu)
Entschuldigt - ich habe hier keine reellen Daten, sondern nur Beispiel verwendet und unterscheiden sich in den beiden Tabellen. Daher der Unterscheid in der zweiten Tabelle zur ersten.

auch in der ersten Tabelle sollte für die id=2 die Gruppe gefüllt sein.

Nein - wenn schon ein Eintrag für das jeweilige Datum existiert, ist das Feld der Gruppe leer (wie bei dem Beispiel gezeigt).
Grundsätzlich ist ein Typ zu jedem Datum dran (WR) und die anderen typen sind optional. Bei den optionalen steht keine Gruppe, da das die macht, die zu dem Datum dran ist.

Es handelt sich um eine MySQL Datenbank.

Verstehe ich das richtig - eine Pivotabfrage ist etwas in der Art wie von Georg beschrieben (IF(ISNULL(gruppe), '', 'X') as "typ ..")?

 

[Georg V.]

Bitte denke daran, dass SQL eine Verarbeitung von Gruppen ist (und kein zeilenbasiertes Verarbeiten von Daten). Es gibt (außer bei Ausgaben und Verwendung von sogenannten Windows-Funktionen) keine Zeilennummern. Wenn Du trotzdem mit solchen Daten arbeiten musst, dann kann als Notbehelf die Gruppe aus dem GROUP BY herausnehmen (da fällt mir auf, dass die Lösung nicht korrekt ist

wenn Du jeden Reinigungstyp ein IF(ISNULL(gruppe), '', 'X') as "typ .." hinzufügst.

und es eher

wenn Du jeden Reinigungstyp ein IF(ISNULL(typ), '', 'X') as "typ .." hinzufügst.

lauten muss.) Für die Spalte Gruppe kommt dann die Aggregationsfunktion max() zum tragen.

 

[akretschmer]

suche mal hier nach meiner FAQ "Zeilen zu Spalten".

 

[dabadepdu]

eine Pivotabfrage ist etwas in der Art wie von Georg beschrieben (IF(ISNULL(gruppe), '', 'X') as "typ ..")?

Das definiert eine Ergebnisspalte.
"Pivot Abfrage" beschreibt die ganze Abfrage. Und zwar eher die, die auch explizite Syntax benutzt.
DB, die das nicht explizit können, nennen das dennoch gerne so, also ihre Nutzer. Es sind aber letztlich Workarounds.
Kannst Du alles im Internet finden und auch ausprobieren- auch wie es anderswo läuft mit Web SQL DB wie dbfiddle.
- Pivot
- Crosstable
- Kreuztabelle

Es gibt auch den umgekehrten Weg, Unpivot. Das Thema ist grad häufig gefragt hier im Forum.

Dabei muss man nicht nur den Entstehungsweg der Abfrage und das Ergebnis betrachten- nach dem Motto, Hauptsache das Ergebnis passt-, sondern auch den Prozess in der DB.
Eine DB, die solcher Befehle mächtig ist, kann das nutzen, um den Ausführungsplan für dieses Verfahren zu optimieren. Bei größeren Abfragen ist das sehr hilfreich (Performance).

 

[vodanet]

Vielen Dank euch für die guten Tipps!
Ich werde versuchen mich in die Thematik zu vertiefen.

Vielleicht habe ich es nicht gut erklärt, daher versuche ich etwas anonymisiert die Ausgaben zu zeigen.

Ihr könnt ja mal drüber schauen. Parallel versuche ich eure Punkte umzusetzen.
Ich hatte eben schon folgendes probiert:

SELECT datum, IF(typ = 'WR', '', 'X') as 'WR',IF(typ = 'ZR', '', 'X') as 'ZR',IF(typ = 'Rasen', '', 'X') as 'RASEN',gruppe.name FROM `plan_typ` LEFT JOIN gruppe ON plan_typ.gruppe=gruppe.id WHERE `datum` > DATE(NOW()) ORDER by datum

Das funktioniert leider nicht, da für (in dem Fall typ=Rasen) immer ein X kommt, ob wohl das nicht stimmt (siehe tabelle ganz unten in dieser Nachricht).

Datum	WR	ZR	WR	gruppe
12.09.2022 06:00	X		X	ES
19.09.2022 06:00	X		X	DV
26.09.2022 06:00	X		X	WT
03.10.2022 06:00	X		X	DJ
10.10.2022 06:00	X		X	MH
17.10.2022 06:00	X		X	ES
24.10.2022 06:00	X		X	DV
31.10.2022 06:00	X		X	WT
07.11.2022 06:00	X		X	DJ
14.11.2022 06:00	X		X	MH
21.11.2022 06:00	X		X	ES
28.11.2022 06:00	X		X	DV
05.12.2022 06:00	X		X	WT
12.12.2022 06:00	X		X	DJ
19.12.2022 06:00	X		X	MH
26.12.2022 06:00	X		X	ES
02.01.2023 06:00	X		X	DV
09.01.2023 06:00	X		X	WT
16.01.2023 06:00	X		X	DJ
23.01.2023 06:00	X		X	MH
30.01.2023 06:00	X		X	ES
06.02.2023 06:00	X		X	DV
13.02.2023 06:00	X		X	WT
20.02.2023 06:00	X		X	DJ
27.02.2023 06:00	X		X	MH

Von der Logik her, würde ich nun ein group by datum machen, aber am Ergebnis sehe ich, dass das nicht stimmt:

Datum	WR	ZR	Rasen	WR
12.09.2022 06:00	X		X	ES
19.09.2022 06:00	X		X	DV
19.09.2022 06:00	X	X	X	NULL
19.09.2022 06:00		X	X	NULL
19.09.2022 06:00		X	X	NULL
19.09.2022 06:00		X	X	NULL
19.09.2022 06:00	X	X	X	NULL
26.09.2022 06:00	X		X	WT
03.10.2022 06:00		X	X	NULL
03.10.2022 06:00		X	X	NULL
03.10.2022 06:00	X		X	DJ
03.10.2022 06:00		X	X	NULL
10.10.2022 06:00	X		X	MH
17.10.2022 06:00		X	X	NULL
17.10.2022 06:00	X	X	X	NULL
17.10.2022 06:00	X		X	ES
17.10.2022 06:00	X	X	X	NULL
17.10.2022 06:00		X	X	NULL
17.10.2022 06:00		X	X	NULL
24.10.2022 06:00	X		X	DV
31.10.2022 06:00		X	X	NULL
31.10.2022 06:00		X	X	NULL
31.10.2022 06:00	X		X	WT
31.10.2022 06:00		X	X	NULL
07.11.2022 06:00	X		X	DJ

Als Background: Die Tabelle reinigung sieht wie folgt aus (SELECT * FROM `plan_reinigung` WHERE `datum` > (NOW()) ORDER by datum):

Hinweis: Gruppe 99 ist keine echt Gruppe. Das bedeutet, dass es die Gruppe macht, die mit typ ZR dran wäre.

id	gruppe	typ	datum
74​	10​	ZR	12.09.2022 06:00
30​	1​	ZR	19.09.2022 06:00
153​	99​	RASEN	19.09.2022 06:00
108​	99​	WR	19.09.2022 06:00
130​	99​	WR	19.09.2022 06:00
119​	99​	WR	19.09.2022 06:00
142​	99​	RASEN	19.09.2022 06:00
20​	11​	ZR	26.09.2022 06:00
120​	99​	WR	03.10.2022 06:00
131​	99​	WR	03.10.2022 06:00
42​	5​	ZR	03.10.2022 06:00
109​	99​	WR	03.10.2022 06:00
53​	9​	ZR	10.10.2022 06:00
110​	99​	WR	17.10.2022 06:00
154​	99​	RASEN	17.10.2022 06:00
75​	10​	ZR	17.10.2022 06:00
143​	99​	RASEN	17.10.2022 06:00
132​	99​	WR	17.10.2022 06:00
121​	99​	WR	17.10.2022 06:00
31​	1​	ZR	24.10.2022 06:00
133​	99​	WR	31.10.2022 06:00
111​	99​	WR	31.10.2022 06:00
21​	11​	ZR	31.10.2022 06:00
122​	99​	WR	31.10.2022 06:00
43​	5​	ZR	07.11.2022 06:00

Beim Anlegen der Einträge in der Tabelle reinigung wurden der Reihe nach, nach Typ die zugeordnete Gruppe für das jeweilige Datum eingetragen.

 

[Georg V.]

Nicht gut übertragen

IF(typ = 'WR', '', 'X') as 'WR',IF(typ = 'ZR', '', 'X') as 'ZR',IF(typ = 'Rasen', '', 'X')

Da Du nicht auf ISNULL prüfst muss der Leerstring nach hinten

IF(typ = 'WR', 'X', '') as 'WR',IF(typ = 'ZR', 'X', '') as 'ZR',IF(typ = 'Rasen', 'X', '')

Und da Du nie den Typ "Rasen" hast (normalerweise hast Du nur 2 Buchstaben im Attribut typ stehen) ist das auch immer Falsch.

Bitte beim Kopieren Deiner Beiträge aufpassen! Die Tabelle und das SQL Statement passen nicht zusammen.

Edit: Tippfehler korrigiert

 

[vodanet]

Ok. Ich habe den Fehler mit ISNULL korrigiert und den Leerstring nach hinten gesetzt.

Und da Du nie den Typ "Rasen" hast (normalerweise hast Du nur 2 Buchstaben im Attribut typ stehen) ist das auch immer Falsch.

Ich habe einen Typ "RASEN" (siehe letzte Tabelle). Bitte entschuldigt. Durch das kopieren aus der Tabelle und das anonymisieren ist mir ein Fehler unterlaufen, den ich eben erst sehe.
In der ersten dargestellten Tabelle gibt es zwei Spalten "WR". Das stimmt nicht. Das wäre die Spalte RASEN.

Hier die korrigierte Abfrage:
SELECT datum, IF(typ = 'WR', 'X', '') as 'WR',IF(typ = 'ZR', 'X', '') as 'ZR',IF(typ = 'RASEN', 'X', '') as 'RASEN',gruppe.name FROM `plan_reinigung` LEFT JOIN gruppe ON plan_reinigung.gruppe=gruppe.id WHERE `datum` > DATE(NOW()) ORDER by datum

datum	WR	ZR	RASEN	name
12.09.2022 06:00		X		ES
19.09.2022 06:00	X			NULL
19.09.2022 06:00		X		DV
19.09.2022 06:00			X	NULL
26.09.2022 06:00		X		WT
03.10.2022 06:00	X			NULL
03.10.2022 06:00		X		DJ
10.10.2022 06:00		X		MH
17.10.2022 06:00		X		ES
17.10.2022 06:00	X			NULL
17.10.2022 06:00			X	NULL
24.10.2022 06:00		X		DV
31.10.2022 06:00		X		WT
31.10.2022 06:00	X			NULL
07.11.2022 06:00		X		DJ
14.11.2022 06:00	X			NULL
14.11.2022 06:00			X	NULL
14.11.2022 06:00		X		MH
21.11.2022 06:00		X		ES
28.11.2022 06:00		X		DV
28.11.2022 06:00	X			NULL
05.12.2022 06:00		X		WT
12.12.2022 06:00	X			NULL
12.12.2022 06:00			X	NULL
12.12.2022 06:00		X		DJ

Jetzt muss ich es nur noch auf eine Reihe bekommen.

 

[Georg V.]

select datum, max(WR) as 'WR', max(ZR) as 'ZR', max(RASEN) as 'Rasen' from (
SELECT datum, IF(typ = 'WR', 'X', '') as 'WR',IF(typ = 'ZR', 'X', '') as 'ZR',IF(typ = 'RASEN', 'X', '') as 'RASEN',gruppe.name FROM `plan_reinigung` LEFT JOIN gruppe ON plan_reinigung.gruppe=gruppe.id WHERE `datum` > DATE(NOW()) ORDER by datum
) q1
group by datum

Man kann immer das Ergebnis eine Abfrage auch als Tabellen nutzen, die abgefragt wird. Nur auf die Testausgabe unter name musst Du verzichten.

 

[vodanet]

Vielen Dank!!
Ich hatte eben eine ähnliche Lösung:
SELECT id,datum, MAX(IF(typ='WR', 'X', NULL)) AS WR, MAX(IF(typ='ZR', 'X', NULL)) AS ZR, MAX(IF(typ='RASEN', 'X', NULL)) AS RASEN FROM plan_reinigung GROUP BY datum ORDER by datum
Ich weiß nicht, welche aus technischer Sicht besser ist.
Habt vielen Dank für eure Geduld und die Hilfe! Ein schönes Wochenende noch!

 

[Georg V.]

Wenn die Zwischenmenge klein ist (und im Cache des DBMS passt und damit nicht auf die Platte geschrieben werden muss) sind beide Lösungen nahezu gleich schnell. Hier ging es mir aber nicht um Performance, sondern dem Aufbauen von (Abfrage-) Logik. Noch viel Spaß mit SQL und Datenbanken.