SQL-Abfrage mit dynamischen Spalten

[Lana]

Hi!

ich möchte, bzw. muss mit SQL (PostgreSQL 7.4) eine Abfrage schreiben, welche die Spaltendynamisch ausgibt.

ein verkürztes Beispiel:
+----------+ +----------+ +----------+
| Tabelle1 | | Tabelle2 | | Tabelle3 |
+----------+ +----------+ +----------+
| id       | | id       | | id       |
+----------+ +----------+ +----------+
| name     | | id_t1    | | name     |
+----------+ +----------+ +----------+
             | id_t3    |
             +----------+
             | text     |
             +----------+

ich möchte jetzt, das es z.B. wie folgt ausgegeben wird

+------------+ +------------+ +------------+
|            | | t1.name(1) | | t1.name(2) |
+------------+ +------------+ +------------+
| t3.name(1) | | t2.text    | | t2.text    |
+------------+ +------------+ +------------+
| t3.name(2) | | t2.text    | | t2.text    |
+------------+ +------------+ +------------+

(1) und (2) sollen die Datensatzids sein

t2.text ist eine Zahl.

Wenn ich die Abfrage z.B. so schreibe

SELECT t1.name, t2.text, t3.name  
FROM tabelle1 t1  
INNER JOIN tabelle2 t2 ON t1.id = t2.id_t1  
INNER JOIN tabelle3 t3 ON t2.id_t3 = t3.id

habe ich zwar soweit alle Informationen, jedoch nicht in dem Format wie ich es benötige.

Hat jemand evtl. ein Tipp, wie ich das realisieren kann?

Lana

[Vinzenz Mai]

Hallo,

ich möchte, bzw. muss mit SQL (PostgreSQL 7.4) eine Abfrage schreiben, welche die Spaltendynamisch ausgibt.

| Tabelle1 | | Tabelle2 | | Tabelle3 |
+----------+ +----------+ +----------+
| id       | | id       | | id       |
| name     | | id_t1    | | name     |
             | id_t3    |
             | text     |

ich möchte jetzt, das es z.B. wie folgt ausgegeben wird

|            | | t1.name(1) | | t1.name(2) |
+------------+ +------------+ +------------+
| t3.name(1) | | t2.text    | | t2.text    |
| t3.name(2) | | t2.text    | | t2.text    |

sieht nach einer Kreutabelle aus. Dazu hab' ich schon diverses gepostet:

</archiv/2008/6/t173119/#m1136008>
</archiv/2006/3/t125385/#m808888>

Mit dem SQL-Dialekt von PostgreSQL sollte sich sicherlich eine Stored Procedure schreiben lassen, die eine solche Abfrage aus allgemeinen Angaben zusammenbaut. Falls es nur diese eine hier ist, schreib' sie nach den Mustern von Hand.

Freundliche Grüße

Vinzenz

[Lana]

Hi!

»» ich möchte jetzt, das es z.B. wie folgt ausgegeben wird
»»
»» |            | | t1.name(1) | | t1.name(2) |
»» +------------+ +------------+ +------------+
»» | t3.name(1) | | t2.text    | | t2.text    |
»» | t3.name(2) | | t2.text    | | t2.text    |

sieht nach einer Kreutabelle aus. Dazu hab' ich schon diverses gepostet:

</archiv/2008/6/t173119/#m1136008>
</archiv/2006/3/t125385/#m808888>

Mit dem SQL-Dialekt von PostgreSQL sollte sich sicherlich eine Stored Procedure schreiben lassen, die eine solche Abfrage aus allgemeinen Angaben zusammenbaut. Falls es nur diese eine hier ist, schreib' sie nach den Mustern von Hand.

im Moment weiß ich nicht so wirklich, wie mir Dein Link helfen soll.
Ich finde keinen Ansatz.

Lana

[Vinzenz Mai]

Hallo Lana,

wo soll ich Dich abholen? Was genau verstehst Du nicht?

» »» ich möchte jetzt, das es z.B. wie folgt ausgegeben wird
» »»
» »» |            | | t1.name(1) | | t1.name(2) |
» »» +------------+ +------------+ +------------+
» »» | t3.name(1) | | t2.text    | | t2.text    |
» »» | t3.name(2) | | t2.text    | | t2.text    |

» sieht nach einer Kreutabelle aus. Dazu hab' ich schon diverses gepostet:

» </archiv/2008/6/t173119/#m1136008>
» </archiv/2006/3/t125385/#m808888>

im Moment weiß ich nicht so wirklich, wie mir Dein Link helfen soll.
Ich finde keinen Ansatz.

füllen wir Deine Beispieltabellen einfach mit ein paar Beispieldaten, um das Vorgehen zu erläutern:

| Tabelle1 | | Tabelle2 | | Tabelle3 |
+----------+ +----------+ +----------+
| id       | | id       | | id       |
| name     | | id_t1    | | name     |
             | id_t3    |
             | text     |

Tabelle1 nenne ich Personen:

id | name
-------------
 1 | Susanne
 2 | Max
 3 | Lana

Tabelle3 nenne ich Artikel

id | name
----------------
 1 | Buch
 2 | Auto

Tabelle2 nenne ich Besitz: Eine Person kann mehrere Artikel besitzen, die gleiche Art von Artikel kann von mehreren besessen werden, die typische n:m-Beziehung (da hattest Du doch neulich einen Thread). Eine Person muss nicht jeden Artikel haben. Die Spalte "text" enthalte die Anzahl der (gleichen) Artikel, die jemand besitzt, ich nenne sie daher um in anzahl.

In meinem Beispiel soll die Zuordnung eindeutig sein, d.h. es könnte ein UNIQUE-Index über den beiden Spalten (id_t1, id_t3). Wenn dies nicht der Fall sein sollte, kompliziert dies die Geschichte, denn da Du in Deiner Ergebnismenge jeder möglichen Kombination von id_t1 und id_t3 genau einen Wert zuordnest.

Besitz

id | id_t1 | id_t3 | anzahl
---------------------------
 1 |   1   |   1   |   50
 2 |   2   |   1   |    7
 3 |   1   |   2   |    2
 4 |   3   |   2   |    1

Die daraus resultierende Ergebnistabelle sähe wie folgt aus

name    | Buch | Auto
---------------------
Susanne |   50 |    2
Max     |    7 | NULL
Lana    | NULL |    1

Du siehst, wie aus jedem Zeilenwert aus Tabelle3 (Artikel) eine Spalte im Ergebnis wird. Falls sich die Einträge in Tabelle3 ändern können, beispielsweise der Artikel Kosmetik dazu käme, kannst Du die Abfrage nur dann "statisch" zusammenstellen, wenn Dein DBMS eine Kreuztabellenabfrage unterstützt (z.B. MS Access, MS SQL Server 2005 oder neuer).

Sonst kannst Du Dir in der ersten Runde die Spaltenliste zusammenstellen, dabei ist name (aus Tabelle 1) bereits gesetzt. Die restlichen Spalten resultieren aus

    SELECT  
        name  
    FROM  
        artikel  
    ORDER BY      -- Sortierung natürlich auch anders möglich  
        id        -- wie die Anforderung eben ist.  

Ergebnis dieser Abfrage mit unseren Beispieldaten:

hame
----
Buch
Auto

Mit Hilfe dieser Daten kann die Abfrage erstellt werden, die das gewünschte Ergebnis liefert:

Wir benötigen für *jede* Person einen Datensatz in der Ergebnismenge, selbst wenn zu dieser kein passender Datensatz in der Besitztabelle ist, daher verwenden wir einen LEFT JOIN von personen auf besitz:

  
SELECT           -- Gib mir  
    p.name       -- zu jedem Eintrag in Tabelle1 (personen)  
    -- berücksichtige nur Zeilen mit dem Wert 1 in b.id_t3  
    -- und von allen diesen Zeilen hätte ich nur das Maximum  
    -- (das ja genau einmal zu einem id_t1-Wert vorkommt)  
    -- und benenne diese Reihe "Buch", weil dies der zum  
    -- Wert 1 in id_t3 passende Artikelname ist  
    MAX([link:http://www.postgresql.org/docs/7.4/interactive/functions-conditional.html#AEN11386@title=CASE] WHEN b.id_t3 = 1 THEN b.anzahl ELSE NULL END) AS Buch,  
    -- und gehe bei der id vom Auto analog vor  
    MAX(CASE WHEN b.id_t3 = 2 THEN b.anzahl ELSE NULL END) AS Auto  
FROM  
    personen p  
LEFT JOIN  
    besitz b  
ON  
    p.id = b.id_t1  

Eigentlich solltest Du die Analogie zum ersten verlinkten Thread jetzt erkennen können.

Mit PL/pgSQL solltest Du in der Lage sein, das entsprechende Statement in einer Stored Procedure dynamisch zusammenzubauen und anschließend auszuführen.

PostgreSQL 7.4 ist allerdings schon recht alt, wenn auch erfreulicherweise immer noch gepflegt.

Freundliche Grüße

Vinzenz