Verwenden Sie SQL, um Werte zu finden, wo es viele zu eins ist

Der erste Teil meines Problems weiß nicht, wie man diese Frage richtig fragen und formulieren muss, also entschuldige ich mich, wenn dies ein Duplikat ist.

Abgesehen von einer User-Tabelle habe ich so einen Tisch

[UserRoles] UserID | RoleName -------|--------- 1 | Role1 1 | Role2 1 | Role3 1 | Role4 2 | Role1 2 | Role4 

Ein weiterer Tisch wie folgt:

 [RoleRoleTypes] RoleName | Function ------------------- Role1 | FunctionA Role2 | FunctionA Role3 | FunctionB Role4 | FunctionC 

Was ich am Ende möchte, ist eine Tabelle mit der UserID , eine durch Kommas getrennte UserID , eine durch Kommas getrennte list von functionen.

Endresultat

 UserId | Roles | Functions -------|---------------------------- |------------------------------------ 1 | Role1, Role2, Role3, Role4 | FunctionA, FunctionB, FunctionC 2 | Role1, Role4 | FunctionC 

Die ersten beiden Spalten sind ziemlich einfach, mit Sachen, um die Komma getrennte list der Rollen zu erstellen. Aber ich bin nicht sicher, wie man über die Berechnung einer list von functionen, dass mehrere Rollen bilden eine function zu gehen. Ein Benutzer muss alle Rollen aufgeführt haben, um die function zu haben. Also, wenn du role1 hast, aber nicht Rolle 2, hast du keine function. Ich habe versucht, eine Vielzahl von Joins aber nie irgendwo in der Nähe der richtigen data. Irgendein Rat?

Hier habe ich für die ersten beiden Spalten

 SELECT u.email, Roles = STUFF( (SELECT ',' + RoleName From UserRoles ur WHERE ur.UserID = u.UserID FOR XML PATH('')), 1, 1, '') FROM Users u 

EDIT Hinzufügen von Klärungs- und Endergebnisbeispiel

Ich denke, Sie müssen nur einen ähnlichen Ansatz wie im Fall von RoleNames verwenden und verwenden, um doppelte functionen zu vermeiden

 SELECT u.userid, Roles = STUFF( (SELECT ', ' + RoleName FROM UserRoles ur WHERE ur.UserID = u.UserID FOR XML PATH('') ), 1, 1, ''), Functions = STUFF( (SELECT distinct ', ' + rt.[function] FROM [UserRoles] ur JOIN [RoleRoleTypes] rt ON ur.RoleName = rt.RoleName WHERE ur.UserID = u.UserID FOR XML PATH('') ), 1, 1, '') FROM [User] u 

Demo

Sie müssen das Distinct- Schlüsselwort in Ihrer Abfrage verwenden, um die Vervielfältigung der Ergebnisse zu vermeiden.