SQL Relation zwischen Datensätzen

[mymoto]

Hallo,

ich komme gerade nicht weiter bei einem Projekt, wo ich nur SQL Abfragen machen kann. Keine Modifikationen, keine Views. Welches Datenbanksystem? KP. Da gibt es nur eine Weboberfläche.

Die Tabellen sehen etwa so aus:

Autos

AutoID | AutoModell
-------------------
1      | Modell A
2      | Modell B
3      | Modell C
4      | Modell D
5      | Modell E
6      | Modell F
7      | Modell G
...


Relationen

RelationID | ParentAutoID | ChildAutoID
---------------------------------------
1          | 1            | 3
2          | 1            | 4
3          | 1            | 5
4          | 2            | 6
5          | 2            | 7
...


Ausgabe aktuell

AutoID | AutoModell | Beziehungstyp | ChildAutoModell
-----------------------------------------------------
1      | Modell A   | verbunden mit | Modell C
1      | Modell A   | verbunden mit | Modell D
1      | Modell A   | verbunden mit | Modell E
2      | Modell B   | verbunden mit | Modell F
2      | Modell B   | verbunden mit | Modell G
...


gewünschte Ausgabe

AutoID | AutoModell | Beziehungstyp | ChildAutoModell | ChildAutoModell2 | ChildAutoModell3
-------------------------------------------------------------------------------------------
1      | Modell A   | verbunden mit | Modell C        | Modell D         | Modell E
2      | Modell B   | verbunden mit | Modell F        | Modell G         | NULL
...

Meine aktuelle Abfrage ist

Select Top 200 Autos.AutoID,
  Autos.AutoModell,
From Autos
  Inner Join Relationen On Relationen.ParentAutoID =
    Autos.AutoID
  Inner Join (
      Select Top 200 Autos.AutoID,
          Autos.AutoModell,
    From Autos) As ChildAuto On Relationen.ChildAutoID =
    ChildAuto.AutoID
Order By Autos.AutoID

Jetzt weiß ich aber nicht, wie ich zur gewünschten Ausgabe komme. Ich denke https://learnsql.com/blog/lead-and-lag-functions-in-sql/ könnte helfen, aber ich muss/könnte ggf. Unterabfragen per Joins verbinden. "Beziehungstyp" bei der Ausgabe kann übrigens weggelassen werden.

Bestimmt gibt es viele Wege zum Ziel. Wichtig ist, dass die Abfrage erweitert werden kann und später sogar dynamisch erweiterbar. Bitte um Hilfe.

 

[RalphS]

Hier steht Käse. 😭

 

[sandreas]

Schau mal da: https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server
Du willst Zeilen in Spalten konvertieren, das ist keine leichte Aufgabe. Aber der Post sollte dir helfen.

 

[efcoyote]

Um die Problematik dieses gewünschten Ergebnisses etwas zu verdeutlichen.

Nehmen wir an deine erste AutoID hat 3 Untermodelle
AutoID | AutoModell | Beziehungstyp | ChildAutoModell | ChildAutoModell2 | ChildAutoModell3

Die zweite aber nur 1 Untermodell:
AutoID | AutoModell | Beziehungstyp | ChildAutoModell | ? | ?
Was steht dann in den letzten beiden Spalten?

Wenn jetzt eine AutoID mit 5 Untermodellen kommt, dann brauchen alle vorherigen Ergebnisse plötzlich 2 Spalten mehr.

Mit SELECT wird das so dynamisch nicht funktionieren, denn dabei muss die Anzahl der Spalten von vornherein feststehen. Am besten lässt sich so etwas programmiertechnisch außerhalb von SQL (z.B. PHP) lösen. Auch ist eine eventuelle Lösung stark von deinem verwendeten Datenbankmanagementsystem abhängig.

 

[mymoto]

Datenbanksystem ist SQL LocalDB.

Mit Pivot könnte ich bestimmt auch zum Ziel kommen, aber bezüglich Erweiterbarkeit müsste ich noch auf zusätzliche Beziehungen achten. 90̂° Drehung macht es etwas schwieriger zu überblicken.

Derweil habe ich es mal mit Joins und lead() gemacht. Zumindest habe ich bis zum 2. Child brauchbare Ergebnisse. Unnötige Datensätze kann man ganz unten beim where noch wegfiltern.

Ungefähr so:

Select Top 200 Autos.AutoID,
  Autos.AutoModell,
  ChildAuto.AutoModell As ChildAutoModell,
  Child2.Child2Modell
From Autos
  Inner Join Relationen On Relationen.ParentAutoID =
    Autos.AutoID
  Inner Join (
      Select Top 200 Autos.AutoID,
          Autos.AutoModell,
    From Autos) As ChildAuto On Relationen.ChildAutoID =
    ChildAuto.AutoID
    -- nächster Child
  Inner Join (Select Top 200 Relationen.RelationID,
      Relationen.ParentAutoID,
      Relationen.ChildAutoID,
      Lead(Relationen.ParentAutoID) Over (Order By
      Relationen.RelationID) As nextparentID,
      Lead(Relationen.ChildAutoID) Over (Order By
      Relationen.RelationID) As nextchildID
    From Relationen) As Child2Pos On Autos.AutoID =
      Child2Pos.ParentAutoID And Child2Pos.nextparentID = Autos.AutoID
-- Werte holen
  Inner Join (Select Top 200 Autos.AutoID As chooseAuto,
      Autos.AutoModell As Child2Modell
    From Autos) As Child2 On
      Child2Pos.nextchildID = Child2Table.chooseAuto
    Where...
Order By Autos.AutoID

Lässt sich mit weiteren Childs erweitern. Kann man es nicht dynamisch mit einer Schleife machen? Mit bestimmten Abfragen könnte ich den Parent mit den meisten Beziehungen finden und die Anzahl herausfinden.

Bei lead() kann man auch Parameter angeben, welche xte Zeile nach unten geschaut werden soll.