pivot mit crosstab als view

Fragen und Probleme rund um die Abfragespache SQL.
Antworten
toba
Neuer Benutzer
Beiträge: 3
Registriert: Do 8. Mär 2018, 09:35

pivot mit crosstab als view

Beitrag von toba » Do 8. Mär 2018, 09:52

Hallo PG-Forum,

ich habe mit PG9.6 unter Zuhilfenahme von pgAdmin3 funktionierende crosstab-selects für Pivot-Ausgaben gebastelt.
Daraus möchte ich nun Views machen. Dabei scheitere ich und habe nun die Hoffnung, dass mir vielleicht hier jemand auf die Sprünge helfen kann.

Hier das funktionierende crosstab-select:

Code: Alles auswählen

select *
    from crosstab ('
        SELECT risa_mitarbeiter.str_nachname, risa_mitarbeiter.str_vorname, ref_mitarbeiter, l_tagnr, sum(l_arbeitszeit)
        FROM risa_monatsterminplan
        JOIN risa_mitarbeiter ON risa_monatsterminplan.ref_mitarbeiter = risa_mitarbeiter.lid
        WHERE l_jahr = 2018
        GROUP BY risa_mitarbeiter.str_nachname, risa_mitarbeiter.str_vorname, ref_mitarbeiter, risa_monatsterminplan.l_tagnr
        ORDER BY 1
        ','
	SELECT DISTINCT l_tagnr
	FROM risa_monatsterminplan
	ORDER BY 1
        ')
    AS ("Nachname" text, "Vorname" text, "ref_mitarbeiter" int, "Sonntag" int, "Montag" int, "Dienstag" int, "Mittwoch" int, "Donnerstag" int, "Freitag" int, "Samstag" int);
Hier die Anweisung für den View:

Code: Alles auswählen

CREATE OR REPLACE VIEW public.risa_crosstabtest1 AS 

select *
    from crosstab ('
        SELECT risa_mitarbeiter.str_nachname, risa_mitarbeiter.str_vorname, ref_mitarbeiter, l_tagnr, sum(l_arbeitszeit)
        FROM risa_monatsterminplan
        JOIN risa_mitarbeiter ON risa_monatsterminplan.ref_mitarbeiter = risa_mitarbeiter.lid
        WHERE l_jahr = 2018
        GROUP BY risa_mitarbeiter.str_nachname, risa_mitarbeiter.str_vorname, ref_mitarbeiter, risa_monatsterminplan.l_tagnr
        ORDER BY 1
        ','
	SELECT DISTINCT l_tagnr
	FROM risa_monatsterminplan
	ORDER BY 1
        ')
    AS ("Nachname" text, "Vorname" text, "ref_mitarbeiter" int, "Sonntag" int, "Montag" int, "Dienstag" int, "Mittwoch" int, "Donnerstag" int, "Freitag" int, "Samstag" int);

ALTER TABLE public.risa_crosstabtest1
  OWNER TO postgres;
pgAdmin macht dann folgenden View daraus:

Code: Alles auswählen

-- View: public.risa_crosstabtest1

-- DROP VIEW public.risa_crosstabtest1;

CREATE OR REPLACE VIEW public.risa_crosstabtest1 AS 
 SELECT crosstab."Nachname",
    crosstab."Vorname",
    crosstab.ref_mitarbeiter,
    crosstab."Sonntag",
    crosstab."Montag",
    crosstab."Dienstag",
    crosstab."Mittwoch",
    crosstab."Donnerstag",
    crosstab."Freitag",
    crosstab."Samstag"
   FROM crosstab('
        SELECT risa_mitarbeiter.str_nachname, risa_mitarbeiter.str_vorname, ref_mitarbeiter, l_tagnr, sum(l_arbeitszeit)
        FROM risa_monatsterminplan
        JOIN risa_mitarbeiter ON risa_monatsterminplan.ref_mitarbeiter = risa_mitarbeiter.lid
        WHERE l_jahr = 2018
        GROUP BY risa_mitarbeiter.str_nachname, risa_mitarbeiter.str_vorname, ref_mitarbeiter, risa_monatsterminplan.l_tagnr
        ORDER BY 1
        '::text, '
	SELECT DISTINCT l_tagnr
	FROM risa_monatsterminplan
	ORDER BY 1
        '::text) crosstab("Nachname" text, "Vorname" text, ref_mitarbeiter integer, "Sonntag" integer, "Montag" integer, "Dienstag" integer, "Mittwoch" integer, "Donnerstag" integer, "Freitag" integer, "Samstag" integer);

ALTER TABLE public.risa_crosstabtest1
  OWNER TO postgres;



Der funktioniert natürlich nicht. Was kann ich tun? Hat jemand vielleicht eine Idee?
Herzlichen Dank fürs Mitdenken :-)

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

Re: pivot mit crosstab als view

Beitrag von akretschmer » Do 8. Mär 2018, 11:17

Schnellschuß: mache das in psql.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

toba
Neuer Benutzer
Beiträge: 3
Registriert: Do 8. Mär 2018, 09:35

Re: pivot mit crosstab als view

Beitrag von toba » Do 8. Mär 2018, 11:41

Herzlichen Dank für die Schnellschussantwort.

Das Problem bestand in den umhüllenden Anführungsstrichen AS (" ... " int).
Es war quasi ein Anfängerfehler und deshalb für mich als Anfänger vom Schicksal auch so vorgesehen ;-).
Mir ist auch klar, dass man select * from lieber lassen sollte und so ...

Wie folgt funktioniert das jetzt für mich:

Code: Alles auswählen

CREATE OR REPLACE VIEW public.risa_crosstabtest1 AS 
 SELECT crosstab.nachname,
    crosstab.vorname,
    crosstab.ref_mitarbeiter,
    crosstab.sonntag,
    crosstab.montag,
    crosstab.dienstag,
    crosstab.mittwoch,
    crosstab.donnerstag,
    crosstab.freitag,
    crosstab.samstag
   FROM crosstab('
        SELECT risa_mitarbeiter.str_nachname, risa_mitarbeiter.str_vorname, ref_mitarbeiter, l_tagnr, sum(l_arbeitszeit)
        FROM risa_monatsterminplan
        JOIN risa_mitarbeiter ON risa_monatsterminplan.ref_mitarbeiter = risa_mitarbeiter.lid
        WHERE l_jahr = 2018
        GROUP BY risa_mitarbeiter.str_nachname, risa_mitarbeiter.str_vorname, ref_mitarbeiter, risa_monatsterminplan.l_tagnr
        ORDER BY 1
        '::text, '
	SELECT DISTINCT l_tagnr
	FROM risa_monatsterminplan
	ORDER BY 1
        '::text) crosstab(nachname text, vorname text, ref_mitarbeiter integer, sonntag integer, montag integer, dienstag integer, mittwoch integer, donnerstag integer, freitag integer, samstag integer);

ALTER TABLE public.risa_crosstabtest1
  OWNER TO postgres;
Trotzdem möchte ich noch Bezug auf die Empfehlung nehmen, es lieber in psql zu machen.
Das klingt für mich danach, dass ich vielleicht generell auf einem Holzweg sein könnte.
Nun ist mir klar, dass ich keine Ausführlichkeit erwarten kann (und nicht erwarte). Ich würde mich jedoch natürlich über eine knappe Erläuterung - warum besser psql - freuen.

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

Re: pivot mit crosstab als view

Beitrag von akretschmer » Do 8. Mär 2018, 11:44

PGAdmin macht manchmal Dinge ungefragt. Z.B., wenn Du mal 'aus Versehen' einen Großbuchstaben hast, fügt es automatisch passende Quotes ein. Man wundert sich dann, daß eine Abfrage a la select * from taBle in PgAdmin geht aber in anderen Anwendungen nicht - oder so. Auch steht es nicht immer zur Verfügung und ist nicht immer so robust, wie man es sich wünscht ...
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

toba
Neuer Benutzer
Beiträge: 3
Registriert: Do 8. Mär 2018, 09:35

Re: pivot mit crosstab als view

Beitrag von toba » Do 8. Mär 2018, 12:03

Vielen Dank für Deine Ergänzungen dazu.
Habe verstanden.
Ich möchte mich nochmals bedanken und wünsche eine schönen Tag.

Antworten