Ausgabe der Abfrage formatieren - neue Spalten

vodanet

Benutzer
Beiträge
18
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
idgruppetypdatum
15wr
2022-05-02 06:00:00
2zr2022-05-02 06:00:00
366ra2022-05-21 06:00:00
42wr2022-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? :

idgruppetyp wrtyp zrtyp radatum
13XX2022-05-02 06:00:00
255XX2022-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
 
Werbung:

Georg V.

Aktiver Benutzer
Beiträge
37
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

Datenbank-Guru
Beiträge
1.179
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

Benutzer
Beiträge
18
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.

Aktiver Benutzer
Beiträge
37
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.
 

dabadepdu

Datenbank-Guru
Beiträge
1.179
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

Benutzer
Beiträge
18
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).

DatumWRZRWRgruppe
12.09.2022 06:00XXES
19.09.2022 06:00XXDV
26.09.2022 06:00XXWT
03.10.2022 06:00XXDJ
10.10.2022 06:00XXMH
17.10.2022 06:00XXES
24.10.2022 06:00XXDV
31.10.2022 06:00XXWT
07.11.2022 06:00XXDJ
14.11.2022 06:00XXMH
21.11.2022 06:00XXES
28.11.2022 06:00XXDV
05.12.2022 06:00XXWT
12.12.2022 06:00XXDJ
19.12.2022 06:00XXMH
26.12.2022 06:00XXES
02.01.2023 06:00XXDV
09.01.2023 06:00XXWT
16.01.2023 06:00XXDJ
23.01.2023 06:00XXMH
30.01.2023 06:00XXES
06.02.2023 06:00XXDV
13.02.2023 06:00XXWT
20.02.2023 06:00XXDJ
27.02.2023 06:00XXMH

:confused:

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

DatumWRZRRasenWR
12.09.2022 06:00XXES
19.09.2022 06:00XXDV
19.09.2022 06:00XXXNULL
19.09.2022 06:00XXNULL
19.09.2022 06:00XXNULL
19.09.2022 06:00XXNULL
19.09.2022 06:00XXXNULL
26.09.2022 06:00XXWT
03.10.2022 06:00XXNULL
03.10.2022 06:00XXNULL
03.10.2022 06:00XXDJ
03.10.2022 06:00XXNULL
10.10.2022 06:00XXMH
17.10.2022 06:00XXNULL
17.10.2022 06:00XXXNULL
17.10.2022 06:00XXES
17.10.2022 06:00XXXNULL
17.10.2022 06:00XXNULL
17.10.2022 06:00XXNULL
24.10.2022 06:00XXDV
31.10.2022 06:00XXNULL
31.10.2022 06:00XXNULL
31.10.2022 06:00XXWT
31.10.2022 06:00XXNULL
07.11.2022 06:00XXDJ

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.

idgruppetypdatum
74​
10​
ZR12.09.2022 06:00
30​
1​
ZR19.09.2022 06:00
153​
99​
RASEN19.09.2022 06:00
108​
99​
WR19.09.2022 06:00
130​
99​
WR19.09.2022 06:00
119​
99​
WR19.09.2022 06:00
142​
99​
RASEN19.09.2022 06:00
20​
11​
ZR26.09.2022 06:00
120​
99​
WR03.10.2022 06:00
131​
99​
WR03.10.2022 06:00
42​
5​
ZR03.10.2022 06:00
109​
99​
WR03.10.2022 06:00
53​
9​
ZR10.10.2022 06:00
110​
99​
WR17.10.2022 06:00
154​
99​
RASEN17.10.2022 06:00
75​
10​
ZR17.10.2022 06:00
143​
99​
RASEN17.10.2022 06:00
132​
99​
WR17.10.2022 06:00
121​
99​
WR17.10.2022 06:00
31​
1​
ZR24.10.2022 06:00
133​
99​
WR31.10.2022 06:00
111​
99​
WR31.10.2022 06:00
21​
11​
ZR31.10.2022 06:00
122​
99​
WR31.10.2022 06:00
43​
5​
ZR07.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.

Aktiver Benutzer
Beiträge
37
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

Benutzer
Beiträge
18
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
WRZRRASENname
12.09.2022 06:00XES
19.09.2022 06:00XNULL
19.09.2022 06:00XDV
19.09.2022 06:00XNULL
26.09.2022 06:00XWT
03.10.2022 06:00XNULL
03.10.2022 06:00XDJ
10.10.2022 06:00XMH
17.10.2022 06:00XES
17.10.2022 06:00XNULL
17.10.2022 06:00XNULL
24.10.2022 06:00XDV
31.10.2022 06:00XWT
31.10.2022 06:00XNULL
07.11.2022 06:00XDJ
14.11.2022 06:00XNULL
14.11.2022 06:00XNULL
14.11.2022 06:00XMH
21.11.2022 06:00XES
28.11.2022 06:00XDV
28.11.2022 06:00XNULL
05.12.2022 06:00XWT
12.12.2022 06:00XNULL
12.12.2022 06:00XNULL
12.12.2022 06:00XDJ

Jetzt muss ich es nur noch auf eine Reihe bekommen. 🤔
 

Georg V.

Aktiver Benutzer
Beiträge
37
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

Benutzer
Beiträge
18
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!:)
 
Werbung:

Georg V.

Aktiver Benutzer
Beiträge
37
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.
 
Oben