Matrix aus Tabellen

[Stoni]

Ich hab ein Problem und stehe etwas auf dem Schlauch ...

Ich habe folgende Tabellen

Lieferant
Name, Anschrift,...
Otto, Hamburg
Conrad, Hirschau

Produkte
Name, Eigenschaft ...
Fernseher, schwarz
Kleid, rot
Lötkolben, blau

WerLiefertWas
Name Lieferant, Name Produkt
Otto, Fernseher
Otto, Kleid
Conrad, Fernseher
Conrad, Lötkolben

Daraus möchte ich (möglichst dynamisch) folgende Tabelle mit einer Abfrage gestalten:

Ergebnisreport
Lieferant, Fernseher, Kleid, Lötkolben
Otto, X, X, O
Conrad, X, O, X

Bitte nicht über den Sinn der Tabellen diskutieren. Das ist nur ein stark vereinfachtes Beispiel. Wichtig ist, dass je Lieferant nur eine Zeile erscheint.

Hat schon mal jemand so was gemacht?

[Vinzenz Mai]

Hallo

Ich habe folgende Tabellen

in welchem Datenbankmanagementsystem, welche Version?

Lieferant
Name, Anschrift,...
Otto, Hamburg
Conrad, Hirschau

Produkte
Name, Eigenschaft ...
Fernseher, schwarz
Kleid, rot
Lötkolben, blau

WerLiefertWas
Name Lieferant, Name Produkt
Otto, Fernseher
Otto, Kleid
Conrad, Fernseher
Conrad, Lötkolben

Daraus möchte ich (möglichst dynamisch) folgende Tabelle mit einer Abfrage gestalten:

Ergebnisreport
Lieferant, Fernseher, Kleid, Lötkolben
Otto, X, X, O
Conrad, X, O, X

Jet-SQL kennt eine Kreuztabellenabfrage. Die meisten anderen SQL-Dialekte nicht. Du könntest allerdings Dein Statement dynamisch aufbauen lassen in Abhängigkeit einer Abfrage mit GROUP BY oder SELECT DISTINCT

Bitte nicht über den Sinn der Tabellen diskutieren. Das ist nur ein stark vereinfachtes Beispiel. Wichtig ist, dass je Lieferant nur eine Zeile erscheint.

Ein ziemlich schlechtes Beispiel. Oder Du hast eine suboptimale Tabellenstruktur.

Wo ist das Problem? GROUP BY und IF reichen fürs erste.

Hat schon mal jemand so was gemacht?

Ja.

Freundliche Grüße

Vinzenz

[stoni]

Hallo

in welchem Datenbankmanagementsystem, welche Version?

MS SQL2000 (Blödes System, aber vorgegeben)

Jet-SQL kennt eine Kreuztabellenabfrage. Die meisten anderen SQL-Dialekte nicht. Du könntest allerdings Dein Statement dynamisch aufbauen lassen in Abhängigkeit einer Abfrage mit GROUP BY oder SELECT DISTINCT

An was dynamisches habe ich auch gedacht. Da ich in Bereich SQL noch grün hinter den Ohren bin, weis ich nicht wie. Mir fehlen noch die Grundlagen (Alles was über normales SELECT hinausgeht)

Ein ziemlich schlechtes Beispiel. Oder Du hast eine suboptimale Tabellenstruktur.

schon möglich - aber auch da sind die Daten schon da. Ich habe kein Einfluß.

Wo ist das Problem? GROUP BY und IF reichen fürs erste.

Ich hab zwei Probleme:
1.) Der Syntax für die Kreuzchen bei vorhanden und O bei nicht vorhanden. (Das ist mit CASE-WHEN Abfrage vielleicht noch zu machen)
2.) Ein einzeiliges Ergebnis für jeden Lieferanten
3.) Der Lieferantenname ist fix, die Produkte können sich verändern

Bisher bekomme ich nur hin:

Lieferant, Produkt
Otto, Fernseher
Otto, Kleid
Conrad, Fernseher
Conrad, Lötkolben

wobei die eigentliche Tabelle verschlüsselt ist:
1,1
1,2
2,1
2,3

Sonnige Grüße

Stoni

[Vinzenz Mai]

Hallo

in welchem Datenbankmanagementsystem, welche Version?

MS SQL2000 (Blödes System, aber vorgegeben)

Warum blöde? MS SQL-Server 2000 ist ein ausgezeichnetes Datenbankmanagementsystem.

An was dynamisches habe ich auch gedacht. Da ich in Bereich SQL noch grün hinter den Ohren bin, weis ich nicht wie. Mir fehlen noch die Grundlagen (Alles was über normales SELECT hinausgeht)

Lasse doch eine Stored Procedure Dein SQL-Statement zusammenbauen.

Wo ist das Problem? GROUP BY und IF reichen fürs erste.

Ich hab zwei Probleme:
1.) Der Syntax für die Kreuzchen bei vorhanden und O bei nicht vorhanden. (Das ist mit CASE-WHEN Abfrage vielleicht noch zu machen)
2.) Ein einzeiliges Ergebnis für jeden Lieferanten
3.) Der Lieferantenname ist fix, die Produkte können sich verändern

Lieferant, Produkt
Otto, Fernseher
Otto, Kleid
Conrad, Fernseher
Conrad, Lötkolben

Kennst Du den LEFT JOIN? Nein? Der sollte Dir behilflich sein. Lesetipp:
Einführung Joins

Mit wievielen verschiedenen Produkten musst Du denn rechnen? Denke daran, dass jedes einzelne eine Spalte ergibt.

wobei die eigentliche Tabelle verschlüsselt ist:
1,1
1,2
2,1
2,3

Aha, das sieht doch gleich viel freundlicher und besser aus. Die Tabellenstruktur scheint viel besser zu sein, als Du es weißt.

Freundliche Grüße

Vinzenz

[stoni]

Hallo Vinzenz,

und wie mache ich das mit den X und O (Bedeutung angekreuzt, nicht angebkreuzt)

Über einen ersten Syntax wäre ich auch dankbar. Wie gesagt bin ziemlicher DB-Neuling.

Sonnige Grüße

stoni

[Vinzenz Mai]

Hallo Stoni,

Über einen ersten Syntax wäre ich auch dankbar. Wie gesagt bin ziemlicher DB-Neuling.

und schon gleich eine Kreuztabelle, na sowas aber auch :-)
Ein kleines Tutorial, getestet mit Transact-SQL, dem SQL-Dialekt des MS SQL-Server:

Gegeben folgende Tabellen:

Tabelle "Lieferanten"
l_id        Lieferant
----------- ---------
1           Otto
2           Conrad

Tabelle "Produkte"
p_id        Produkt
----------- ----------
1           Fernseher
2           Kleid
3           Lötkolben

Tabelle "WerLiefertWas"
l_id        p_id
----------- -----------
1           1
1           2
2           1
2           3
2           3

Bitte beachte, dass in der Tabelle "WerLiefertWas" die Kombination Conrad/Lötkolben gleich zweimal auftaucht. Dies ist beabsichtigt, es bedeutet "Conrad liefert zwei unterschiedliche Lötkolben". Die restlichen Eigenschaften habe ich hier einfach weggelassen.

Wenn uns nun wirklich interessiert, welcher Lieferant welches Produkt liefert, so bekommen wir diese Informationen über den INNER JOIN

  
SELECT  
 L.Lieferant,  
 P.Produkt  
FROM  
 (Lieferanten L  
INNER JOIN WerLiefertWas W  
ON L.l_id = W.l_id)  
INNER JOIN Produkte P  
ON W.p_id = P.p_id;  

Das Ergebnis dieser Abfrage sieht wie folgt aus:

Lieferant Produkt
--------- ----------
Otto      Fernseher
Otto      Kleid
Conrad    Fernseher
Conrad    Lötkolben
Conrad    Lötkolben

Mehr zu JOINs findest Du in Fortgeschrittene Joins.

Nun zum trickreichen Teil, der Erzeugung der Kreuztabelle:

Der erste Schritt ist noch ganz einfach. Wir nutzen GROUP BY, um gleichartige Datensätze zu gruppieren:

SELECT  
    Lieferant  
FROM  
    (Lieferanten L  
INNER JOIN WerLiefertWas W  
ON L.l_id = W.l_id)  
INNER JOIN Produkte P  
ON W.p_id = P.p_id  
GROUP BY Lieferant

Dieses Ergebnis:
Lieferant
---------
Conrad
Otto

(2 Zeile(n) betroffen)

hätten wir auch einfacher bekommen können. Es ist ja nur der erste Schritt.

Nun müssen wir noch aus den einzelnen Produkten Spalten erzeugen und vor allem den richtigen Wert an die richtige Stelle schreiben. Dazu nutzen wir aus, dass der Buchstabe 'X' in der Sortierreihenfolge größer ist als der Buchstabe 'O' (ein O wie in Ohren :-)).

SELECT  
    Lieferant,  
    MAX(CASE WHEN Produkt = 'Fernseher' THEN 'X' ELSE 'O' END) AS 'Fernseher',  
    MAX(CASE WHEN Produkt = 'Kleid'     THEN 'X' ELSE 'O' END) AS 'Kleid',  
    MAX(CASE WHEN Produkt = 'Lötkolben' THEN 'X' ELSE 'O' END) AS 'Lötkolben'  
FROM  
    (Lieferanten L  
INNER JOIN WerLiefertWas W  
ON L.l_id = W.l_id)  
INNER JOIN Produkte P  
ON W.p_id = P.p_id  
GROUP BY Lieferant

liefert das gewünschte Ergebnis:
Lieferant Fernseher Kleid Lötkolben
--------- --------- ----- ---------
Conrad    X         O     X
Otto      X         X     O

(2 Zeile(n) betroffen)

Wenn ein Lieferant ein bestimmtes Produkt liefert, dann gibt es ein 'X', liefert er es mehrfach, dann gibt es entsprechend viele 'X', gibt es das Produkt nicht, dann gibt es ein 'O'. Zurückgeliefert wird das Maximum, das sobald mindestens ein 'X' da ist, d.h. sobald der Lieferant das Produkt liefert, ein 'X'. Wenn der Lieferant dieses Produkt nicht liefert, dann gibt es ein 'O'.
Du solltest leicht erkennen können, wie du diese Abfrage in Abhängigkeit vom Inhalt der Tabelle Produkte skriptgesteuert erstellen kannst.

Für jeden unterschiedlichen Eintrag in der Tabelle Produkte musst Du Deinem SQL-Statement eine Zeile der Form

[code lang=sql]MAX(CASE WHEN Produkt = 'Eintrag' THEN 'X' ELSE 'O' END) AS 'eintrag'

hinzufügen. Achte dabei auf richtige Kommasetzung. Viel Erfolg!

Freundliche Grüße

Vinzenz

[stoni]

Hallo Vinzenz,

super, danke. Das ist das was ich brauch.

Ich teile die Einschätzung nicht gerade mit dem einfachsten begonnen zu haben. :-)

Jetzt habe ich noch einen Wunsch:

Es kann ja sein, dass sich die Produkte mehr werden. Dann will ich nicht jedes mal den Abfragetext anpassen. Gibt es eine art Schleife, wie man den Teil

MAX(CASE WHEN Produkt = 'Fernseher' THEN 'X' ELSE 'O' END) AS 'Fernseher',
    MAX(CASE WHEN Produkt = 'Kleid'     THEN 'X' ELSE 'O' END) AS 'Kleid',
    MAX(CASE WHEN Produkt = 'Lötkolben' THEN 'X' ELSE 'O' END) AS 'Lötkolben'

auch dynamisch erzeugen kann?

Wenn es hier eine Lösung gibt wäre ich glücklich.

Sonnige Grüße

Stoni

[Vinzenz Mai]

Hallo Stoni,

Es kann ja sein, dass sich die Produkte mehr werden. Dann will ich nicht jedes mal den Abfragetext anpassen. Gibt es eine art Schleife, wie man den Teil

ja, gibt es.

MAX(CASE WHEN Produkt = 'Fernseher' THEN 'X' ELSE 'O' END) AS 'Fernseher',
    MAX(CASE WHEN Produkt = 'Kleid'     THEN 'X' ELSE 'O' END) AS 'Kleid',
    MAX(CASE WHEN Produkt = 'Lötkolben' THEN 'X' ELSE 'O' END) AS 'Lötkolben'

Ich habe die Aufgabe mit einer Stored Procedure gelöst. Diese ist speziell auf Deine Problemstellung mit meinen Tabellen hin getrimmt. Spannend wäre es, diese in eine allgemeine SP für beliebige Kreuztabellen umzuschreiben.
Mein Code ist sicherlich nicht der effizienteste, aber er tut, was er soll:

  
CREATE PROCEDURE kreuztabelle  
AS  
  
-- zwei lokale Variablen werden benötigt:  
DECLARE @sql varchar(8000)     -- um die SQL-Anweisung zusammenzubauen  
                               -- Länge ist auf 8000 Zeichen begrenzt.  
DECLARE @produkt varchar(255)  -- für das aktuelle Produkt  
                               -- sinnvollerweise gleiche Größe wie  
                               -- die Spalte aus der gelesen wird  
  
-- Zusammenbauen der SQL-Anweisung  
-- Zunächst der statische Vorspann  
SET @sql = 'SELECT  
    Lieferant'  
  
-- Einlesen der verschiedenen Produkte in einen [link:http://de.wikipedia.org/wiki/Cursor@title=Cursor] (CURrent Set Of Records)  
DECLARE C CURSOR FOR  
    -- DISTINCT vermeidet Dubletten  
    -- Produkte ohne Produktbezeichnung interessieren auch nicht :-)  
    SELECT DISTINCT Produkt FROM Produkte WHERE Produkt IS NOT NULL  
OPEN C  
  
-- Hole erste Zeile  
FETCH NEXT FROM C INTO @produkt  
-- Solange Ergebnisse da sind  
WHILE @@fetch_status = 0  
BEGIN  
    -- Behandle einfache Anführungszeichen  
    set @produkt = replace(@produkt, '''', '''''')  
  
    -- erzeuge für das Produkt eine Spalte nach dem bewährten Schema  
    SET @sql = @sql + ',  
    '  
        + 'MAX(CASE WHEN Produkt = '''  
        + @produkt  
        + ''' THEN ''X'' ELSE ''O'' END) AS '''  
        + @produkt  
        + ''''  
    -- hole nächste Zeile  
    FETCH NEXT FROM C INTO @produkt  
END  
CLOSE C         -- Schliesse Cursor  
DEALLOCATE C    -- Entferne den Cursorverweis  
                -- Speicher kann freigegeben werden  
  
-- Hänge den statischen Endteil an das SQL-Statement  
SET @sql = @sql + '  
FROM  
 (Lieferanten L  
INNER JOIN WerLiefertWas W ON L.l_id = W.l_id)  
INNER JOIN Produkte P ON W.p_id = P.p_id  
GROUP BY Lieferant'  
  
-- Führe das SQL-Statement aus  
EXEC (@sql)  

Nach einmaligem Ausführen dieser Anweisung kannst Du Deine Kreuztabelle einfach mit der Anweisung

EXEC kreuztabelle

erstellen

Freundliche Grüße

Vinzenz

[stoni]

Hallo Vinzenz,

Gegeben folgende Tabellen:

Wenn die Tabelle Liferanten ergänzt ist:

Tabelle "Lieferanten"
l_id        Lieferant Ort

---

1           Otto      Hamburg
2           Conrad    Hirschau

warum geht dann .. nicht?

SELECT

Lieferant,

Ort,

MAX(CASE WHEN Produkt = 'Fernseher' THEN 'X' ELSE 'O' END) AS 'Fernseher',
    MAX(CASE WHEN Produkt = 'Kleid'     THEN 'X' ELSE 'O' END) AS 'Kleid',
    MAX(CASE WHEN Produkt = 'Lötkolben' THEN 'X' ELSE 'O' END) AS 'Lötkolben'
FROM
    (Lieferanten L
INNER JOIN WerLiefertWas W
ON L.l_id = W.l_id)
INNER JOIN Produkte P
ON W.p_id = P.p_id
GROUP BY Lieferant

>   

[Vinzenz Mai]

Hallo Stoni,

Wenn die Tabelle Liferanten ergänzt ist:

Tabelle "Lieferanten"
l_id        Lieferant Ort

---

1           Otto      Hamburg
2           Conrad    Hirschau

warum geht dann .. nicht?

SELECT

Lieferant,
Ort,

-- weil Du nach jeder Spalte, auf die keine Aggregatsfunktion
-- angewandt wird, gruppieren musst. Das einzige mir bekannte
-- DBMS, das die extrem nervende Angewohnheit hat, soetwas nicht
-- als Syntax-Error durchgehen zu lassen, ist MySQL. Ein ständige
-- Fehlerquelle, die für viele Postings hier im Forum sorgt.

-- Da der Ort für alle Lieferanten gleich sein sollte (wir
-- vernachlässigen hier die Filialen :-)), reicht es Ort der
-- GROUP-BY-Klausel hinzuzufügen

MAX(CASE WHEN Produkt = 'Fernseher' THEN 'X' ELSE 'O' END) AS 'Fernseher',
    MAX(CASE WHEN Produkt = 'Kleid'     THEN 'X' ELSE 'O' END) AS 'Kleid',
    MAX(CASE WHEN Produkt = 'Lötkolben' THEN 'X' ELSE 'O' END) AS 'Lötkolben'
FROM
    (Lieferanten L
INNER JOIN WerLiefertWas W
ON L.l_id = W.l_id)
INNER JOIN Produkte P
ON W.p_id = P.p_id
-- GROUP BY Lieferant

GROUP BY Lieferant, Ort

  
  
Freundliche Grüße  
  
Vinzenz

[Christoph Schnauß]

hallo,

Bitte nicht über den Sinn der Tabellen diskutieren.

Nunja, wer möchte nicht auch mal eine "Matrix" entwickeln. Laut "Matrix Reloaded", was unlängst auf PRO7 lief, braucht man ja eh nur einen Schlüsselmacher, und alles wird gut *g*

Grüße aus Berlin

Christoph S.