Erzeugen eines "Streudiagramms" von Mitgliedern gegen Mengen aus einer SQL-Abfrage

Question

Ich habe eine staff Datenbanktabelle, die die Mitarbeiter enthält, mit user_no y user_name Spalten. Ich habe eine andere, department Tabelle mit den Abteilungen, in denen Mitarbeiter Mitglied sein können, mit dept_no y dept_name als Spalten.

Da Mitarbeiter in mehreren Abteilungen tätig sein können, habe ich eine dritte, staff_dept Tabelle mit einer user_no Spalte und eine dept_no Spalte, die die Primärschlüssel dieser beiden anderen Tabellen sind. Diese Tabelle zeigt, zu welchen Abteilungen jeder Mitarbeiter gehört, und enthält eine Zeile für jede Kreuzung zwischen Benutzer und Abteilung.

Ich möchte eine Ausgabe in Form einer Kalkulationstabelle (CSV-Datei, was auch immer; ich werde die Ergebnisse in eine brauchbare Form bringen, nachdem ich sie erhalten habe) mit einer Spalte für jede Abteilung und einer Zeile für jeden Benutzer, mit einer X die an jeder Kreuzung auftreten, wie in staff_dept .

Kann ich eine einzige SQL-Abfrage schreiben, die dieses Ergebnis erzielt, oder muss ich "richtig" programmieren (denn ein "richtiges" Programm ist es erst, wenn man drei oder vier Abfragen verschachtelt hat)? for Schleifen), um diese Daten zu sammeln und zu formatieren?

Answer 1

Dies kann mit einer PIVOT-Tabelle (mit SQL Server) geschehen:

SELECT user_name, [dept1name], [dept2name], [dept3name], ...
FROM
    (SELECT s.user_name, d.dept_name,
     case when sd.user_no is not null then 'X' else '' end as matches
     from staff s
     cross join department d
     left join staff_dept sd on s.user_no = sd.user_no and d.dept_no = sd.dept_no
    ) AS s
PIVOT
(
    min(matches)
    FOR dept_name IN ([dept1name], [dept2name], [dept3name], ...)
) AS pvt
order by user_name

Demo: http://www.sqlfiddle.com/#!3/c136d/5

Editar : Um die PIVOT-Abfrage dynamisch aus der Liste der Abteilungen in der Tabelle zu generieren, verwenden Sie dynamisches SQL, d. h. Sie generieren den Code in einer Variablen und verwenden sp_executesql gespeicherte Hilfsprozedur. Hier ist ein Beispiel: http://www.sqlfiddle.com/#!3/c136d/14

Answer 2

In SQL Server (wenn Sie SQL Server verwenden), würde ich mit einer vollständigen äußeren Verknüpfung beginnen (um die tous Mitarbeiter und Abteilungen, nicht nur die, die an der Beziehung beteiligt sind), fügen Sie dies in eine Pivot-Anweisung ein, um alle Abteilungen in Spalten zu pivotieren, und erstellen Sie dann ein kurzes Skript, um diese SELECT-Anweisung zu generieren und dynamisch auszuführen (da die durch eine Pivot-Anweisung erstellten Spalten fest kodiert sein müssen, können sie zur Laufzeit nicht dynamisch generiert werden).

Hier ist ein Beispiel -- Es handelt sich um eine Unpivot-Anweisung, aber das Konzept ist so ziemlich dasselbe.