Tabelle "pivotieren"

Fragen und Probleme rund um die Abfragespache SQL.
Antworten
Hanno
Benutzer
Beiträge: 69
Registriert: Do 21. Jan 2010, 15:19

Tabelle "pivotieren"

Beitrag von Hanno » Di 6. Nov 2018, 09:38

Hallo,
Ich habe heute eine Frage und hoffe mir kann hier jemand behilflich sein. ich bedanke mich schon im Voraus.
Ich habe folgende Tabelle:

id_kurs | mitarbeiter | orgnaisationseinheit | kursname | anzahl_stunden | studienjahr
1 | abc | gruppe1 | kurs1 | 10 | 2018
2 | def | gruppe2 | kurs1 | 11 | 2018
3 | ghi | gruppe2 | kurs2 | 9 | 2017
4 | jkl | gruppe3 | kurs3 | 8 | 2018

Ich möchte jetzt die Tabelle "drehen" sodass ich für jeden Kurs eine Zeile habe und dann für jeden Mitarbeiter eine Spalte mit den entsprechenden Stunden. Da die Mitarbeiter je nach Kurs dynamisch sind, also je Kurs immer unterschiedliche Mitarbeiter weiss ich nicht genau ob das überhaupt geht. Ich habe etwas mit crosstab() probiert, aber komme da irgendwie zu keinem Ergebnis. Hat hier vielleicht jemand eine Idee wie man so etwas umsetzen könnte und ob das überhaupt machbar ist?

LG
Hanno

akretschmer
Erfahrener Benutzer
Beiträge: 7513
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Tabelle "pivotieren"

Beitrag von akretschmer » Di 6. Nov 2018, 10:06

Code: Alles auswählen

test=*# select * from hanno ;
 id | mitarbeiter | org_einheit | kursname | stunden | jahr 
----+-------------+-------------+----------+---------+------
  1 | abc         | gruppe1     | kurs1    |      10 | 2018
  2 | def         | gruppe2     | kurs1    |      11 | 2018
  3 | ghi         | gruppe2     | kurs2    |       9 | 2017
  4 | jkl         | gruppe3     | kurs3    |       8 | 2018
(4 rows)

test=*# select kursname, sum(stunden) filter (where mitarbeiter = 'abc') as abc, sum(stunden) filter (where mitarbeiter = 'def') as def, sum(stunden) filter (where mitarbeiter = 'ghi') as ghi, sum(stunden) filter (where mitarbeiter = 'jkl') as jkl from hanno group by kursname order by kursname;
 kursname | abc | def | ghi | jkl 
----------+-----+-----+-----+-----
 kurs1    |  10 |  11 |     |    
 kurs2    |     |     |   9 |    
 kurs3    |     |     |     |   8
(3 rows)

test=*# 
Du wirst das SQL dynamisch bauen müssen, in einer stored function, und mittels EXECUTE ausführen.


Alternativ: prüfe, ob Du das wirklich brauchst. Was ist das Ziel?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

castorp
Erfahrener Benutzer
Beiträge: 1070
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Tabelle "pivotieren"

Beitrag von castorp » Di 6. Nov 2018, 10:13

Das ist nicht wirklich machbar. Bei einer SQL Abfrage muss vor dem Ausführen der Abfrage klar sein, wie viele Spalten im Ergebnis vorhanden sind. Wenn Du vorher alle Mitarbeiter kennst, geht das mit der Lösung von akretschmer (weil Du vorher weißt wie viele Spalten das Ergebnis haben wird).

Aber ich vermute, dass Du die Anzahl der Mitarbeiter vor der Ausführung aber gar nicht wirklich kennst oder es zu viele Mitarbeiter sind, um das so umsetzen zu können.

Ein etwas hässlicher Workaround wäre die Mitarbeiter und deren Stunden in zwei Spalten als Arrays zurückzuliefern.

Code: Alles auswählen

select id_kurs, 
       array_agg(mitarbeiter order by studienjahr) as mitarbeiter, 
       array_agg(anzahl_stunden order by studienjahr) as anzahl_stunden
from kurse
group by id_kurs
order by id_kurs;
Der erste Eintrag im Array für die Mitarbeiter gehört zum ersten Eintrag der Stunden, der zweite Mitarbeiter zu dem zweiten Eintrag der Stunden usw. Vielleicht willst Du das aber pro Jahr und Kurs haben, dann müsstest Du das GROUP BY entsprechend anpassen.

Aber wirklich schön ist das auch nicht.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Hanno
Benutzer
Beiträge: 69
Registriert: Do 21. Jan 2010, 15:19

Re: Tabelle "pivotieren"

Beitrag von Hanno » Di 6. Nov 2018, 11:05

Hallo,

Danke für die schnelle Antwort. Genau das habe ich mir auch schon gedacht. Der Grund warum ich das machen wollte/musste war das die Stunden bisher in einem Excel erfasst wurden. und dort halt pro Organisationseinheit ein eigenes Excel Sheet besteht. Im Excel Sheet war das halt aufgebaut das in den Spalten die Mitarbeiter stehen und in den Zeilen die Kurse. Dort sind dann die Mitarbeiter auch klar abgrenzbar, also immer diesselben. Um möglichst wenig Umgewöhnung zu haben sollte das also am besten wieder so aufgebaut sein. Jetzt sollen diese vielen Excel Sheets aber in einer DB zusammengeführt werden. Ab da sind es dann eben nicht mehr immer diesselben. Deshalb hatte ich das halt auch anders aufgebaut. Aber wie ihr schon richtig sagt ist es viel sinnvoller das so aufzubauen wie ich es auch schon gelöst hatte. Wollte nur sicher gehen, dass es "anders rum" sehr viel komplizierter und unschöner ist. Vielen Dank für die Inputs.

Gisman
Erfahrener Benutzer
Beiträge: 1108
Registriert: Fr 20. Jan 2006, 15:30
Wohnort: Pfalz
Kontaktdaten:

Re: Tabelle "pivotieren"

Beitrag von Gisman » Fr 16. Nov 2018, 14:52

So am Rande es gibt auch die tablefunc-Extension, da gibt es die crosstab-Funktion. Allerdings muss auch da feststehen, was an Spalten vorkommen soll und so richtig schön ist das auch nicht. Allerdings gibt es auch dafür Procedures, die das dynamisieren können, hab das leiden noch nicht umgesetzt.

Gruß Uwe

Edit: Sorry habs überlesen. Man kann das auch mit dem JSON Datenformat machen, aber wie bisher, auch da will vorher festgelegt sein, was in welcher Reihenfolge erscheinen soll. Hab grad kein eingängiges Beispiel, also kurz umrissen, man aggregiert keys und values als arrays mit json_object zu einem JSON-Objekt, bei mir in einem Subselect. Auf das greift man dann mit lateral ( select * from json_to_record(json) as (Liste der keys und Formate) zu.

Antworten