Many to Many-Beziehung durch PIVOT in einer Zeile darstellen

Question

Hallo,

 

ich bastle gerade an einer Abfrage bei der ich nicht wirklich weiterkomme. Vereinfacht gibt es eine Tabelle Kursanmeldungen und eine Tabelle Kurse zwischen denen eine n:m-Beziehung besteht.

Beispiel für eine Anmeldung:

ID.........Vorname......Nachname.....Datum

1..........Max..............Muster..........01.10.2011

 

Und natürlich gehören noch zu einer Anmeldung die Kurse die er gebucht hat:

ID.........Bezeichnung.....................Zeitpunkt

2..........Kurs 2.............................10.10.2011 10:00:00

3..........Kurs 4.............................11.10.2011 11:00:00

 

Wenn ich jetzt beides zusammen abfrage mit einem Join bekomme ich natürlich zwei Zeilen und die Daten, die zur Anmeldung gehören redundant.

 

IDAnmeldung....Vorname......Nachname........Datum..............IDKurs.......Kursbezeichnung........Zeitpunkt

1.....................Max.............Muster..............01.10.2011......2...............Kurs 2.......................10.10.2011 10:00:00

1.....................Max.............Muster..............01.10.2011......4...............Kurs 4.......................11.10.2011 11:00:00

 

Geschmeider für die Nachbearbeitung und der Exportierung als CSV wäre es allerdings, wenn eine Abfrage möglich wäre, deren Ergebnis folgende Form hat. Ich brauche nämlich im CSV die Detaildaten zu einem Kurs nicht. Ich muss nur wissen, welche Kurse die jeweilige Person belegen wird:

 

ID....Vorname........Nachname......Datum..........Kurs 1...........Kurs 2........Kurs 3...........Kurs 4

1.....Max................Muster..........01.10.2011....0..................1...............0..................1

 

Das Ergebnis soll immer dieselbe Anzahl von Spalten haben. Besucht die Person einen Kurs nicht, steht eben eine 0 dabei, ansonsten 1.
Das sieht jetzt auf den ersten Blick nach einem Anwendungsfall für Pivot aus, nur weiß ich nicht über welches Feld ich pivotieren soll, weil ich in diesem Sinne ja kein Wertfeld (irgendwas numerisches) habe. Ich muss zugeben, dass ich so meine Schwierigkeiten mit PIVOT habe und da noch etwas unsicher bin. Ist mein Problem mit Pivot zu lösen oder müsste man das anders machen?

Ich bin für alle Tipps dankbar.

 

Vielen Dank für eure Zeit.

Answer 1

Ein Beispielskript wäre hilfreich. Im Besonderen da die Zwischentabelle zu fehlen scheint.

Answer 2

Hallo Charles,

wie Stefan schon schrieb, fehlt rgendwie eine Zwischentabelle, so das nicht ersichtlich ist wie die Daten miteinander verknüpft sind. Aber im Grunde brauchst Du nur jeweils abzufragen, ob es für entsprechende Person und Kurs ein Eintrag in der Tabelle vorliegt und anhand des Ergebnisses das Feld mt 1 oder 0 zu füllen. Ein (unvollständiges) Beispiel könnte so aussehen:

 

SELECT	ID
		,Vorname
		,Nachname
		,Datum
		,'Kurs 1' = 
			CASE 
				WHEN EXISTS (SELECT * FROM Kurse WHERE Bezeichnung = 'Kurs 1') 
				THEN '1' ELSE '0'
			END
		,'Kurs 2' = 
			CASE 
				WHEN EXISTS (SELECT * FROM Kurse WHERE Bezeichnung = 'Kurs 2') 
				THEN '1' ELSE '0'
			END
		,'Kurs 3' = 
			CASE  
				WHEN EXISTS (SELECT * FROM Kurse WHERE Bezeichnung = 'Kurs 3') 
				THEN '1' ELSE '0'
			END
		,'Kurs 4' = 
			CASE 
				WHEN EXISTS (SELECT * FROM Kurse WHERE Bezeichnung = 'Kurs 4') 
				THEN '1' ELSE '0'
			END
FROM Anmeldung

 

---

Gruß Falk

Answer 3

Hallo,

danke für eure Antworten. Ich habe mein Problem bereits gelöst, auch wenn es nicht ganz optimal ist, passt es. Ich muss meine View, die ich geschrieben habe nämlich immer ändern, wenn ein Kurs hinzu- oder wegkommt.

Die Datenbank ist so aufgebaut, dass es Anmeldungen gibt, Kurse und Zeitpunkte zu denen diese Kurse stattfinden. Es gibt daher eine Tabelle Kurszeitpunkte, die auf einen Zeitpunkt und einen dazugehörigen Kurs verweist (diese verknüpft), weil ein Kurs ja an mehreren Zeitpunkten stattfinden kann.

Es gibt eine Zwischentabelle zwischen Anmeldungen und Kurszeitpunkten, die Tabelle Kursanmeldungen.
An der Benennung der Tabellen müsste ich mal arbeiten. Fand selbst ich verwirrend.

 

Ich habe Falks Beispiel geändert und eine Funktion geschrieben, die 0 oder 1 zurückgibt, je nachdem, ob bei einer Anmeldung ein Kurs gebucht wurde oder nicht. Die Abfrage sieht nun so aus:

 

SELECT ID AS IDAnmeldung,
       Datum AS Anmeldedatum,
       dbo.HatKursGebucht(ID, 1) AS [Kurs 1],
       dbo.HatKursGebucht(ID, 2) AS [Kurs 2],
       dbo.HatKursGebucht(ID, 3) AS [Kurs 3],
       dbo.HatKursGebucht(ID, 4) AS [Kurs 4],
       dbo.HatKursGebucht(ID, 5) AS [Kurs 5]
FROM Anmeldungen;

Die Funktion um zu überprüfen, ob jemand einen Kurs gebucht hat sieht so aus:

 

DECLARE @hatKursGebucht BIT = 0;
        
IF EXISTS(SELECT k.ID
          FROM Anmeldungen AS a
          JOIN Kursanmeldungen AS ka
            ON a.ID = ka.IDAnmeldung
          JOIN Kurszeitpunkte AS kzp
            ON ka.IDKurszeitpunkt = kzp.ID
          JOIN Kurse AS k
            ON k.ID = kzp.IDKurs
          WHERE a.ID = @idAnmeldung
            AND k.ID = @idKurs)
BEGIN
    SET @hatKursGebucht = 1;
END;

RETURN @hatKursGebucht;

Wie gesagt, ändern sich die Kurse, dann müsste ich die View ändern, was natürlich nicht optimal ist. Man könnte sich mit Dynamic SQL aber auch den letzten Teil der View, bei dem ich die Funktion aufrufe auch zusammenbasteln und eben vorher abfragen was für Kurse es gibt.

 

Answer 4

Sorry, schaut übel aus, da es nicht unbedingt viel mit relationalen Datenbanken zu tun hat.

Poste halt bitte ein Beispielskript...