Frage zu SELECT-Statement

Question

Hallo,

ich brauche Hilfe bei der Formulierung des folgenden SELECT-Statements. Ich habe drei Tabellen Movies, Actors sowie Actors_in_Movies. Mit dem Statement

SELECT    m.name, a.actor AS Expr1
FROM      dbo.Movies AS m INNER JOIN
             dbo.Actors_in_Movies AS am ON m.id = am.movie_id INNER JOIN
             dbo.Actors AS a ON a.id = am.actor_id

bekomme ich folgendes Ergebnis:

Titanic  	Leonardo DiCaprio
Titanic   	Kate Winslet
Titanic  	Billy Zane
Pulp Fiction	John Travolta
Pulp Fiction	Samuel Jackson

Wie bekomme ich es hin, dass die Schauspieler aggregiert werden, also ein String CONCAT pro Ergebniszeile wie folgt:

Titanic 	     Leonardo DiCaprio, Kate Winslet, Billy Zane
Pulp Fiction	John Travolta, Samuel Jackson
...

Answer 1

Hallo,

vorausgesetzt, Du verwendest MS SQL Server 2005 oder höher, dann kannst Du die FOR XML PATH Option verwenden. Das hängende Komma könnte man noch abschneiden, wenn es einem stört.

CREATE TABLE #mv

    (film varchar(20), act varchar(50));

INSERT INTO #mv VALUES ('Titanic', 'Leonardo DiCaprio');

INSERT INTO #mv VALUES ('Titanic', 'Kate Winslet');

INSERT INTO #mv VALUES ('Titanic', 'Billy Zane');

INSERT INTO #mv VALUES ('Pulp Fiction', 'John Travolta');

INSERT INTO #mv VALUES ('Pulp Fiction', 'Samuel Jackson');

GO

 

SELECT film

      ,CAST((SELECT (SELECT act + ','

                     FROM #mv AS SUB

                     WHERE SUB.film = MAIN.film

                     FOR XML PATH (''), Type

                    ) AS [Text()]

            ) AS NVARCHAR(MAX)

           ) AS actlist

FROM (SELECT DISTINCT film

      FROM #mv

     ) AS MAIN

 

GO

DROP TABLE #mv;

 

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 2

Danke Olaf Helper,

dein Beispiel konnte ich soweit nachvollziehen. Aber wo bringe ich in deinem Statement von oben die notwenige Join-Verkettung unter?

Du hast oben eine Hilfstabelle #mv benutzt, in der Filme und die Schauspieler bereits aggregiert sind. Ich jedoch habe zwei einzelne Tabellen Movies und Actors, sowie eine Verknüpfungstabelle Actors_in_Movies (die jeweils movie_id und actor_id als Fremdschlüssel enthält), die ich aktuell wie folgt verknüpfe:

SELECT DISTINCT dbo.Movies.name, dbo.Actors.actor
FROM      dbo.Movies INNER JOIN
             dbo.Actors_in_Movies ON dbo.Movies.id = dbo.Actors_in_Movies.movie_id INNER JOIN
             dbo.Actors ON dbo.Actors.id = dbo.Actors_in_Movies.actor_id

Auch nach einigen Versuchen bin ich nicht in der Lage, dein Beispiel an meine Struktur anzupassen ... hast du ggf. noch einen Tip für mich? 

 

Answer 3

Es ist denormalisiert, nicht aggregiert. Es sollte ja auch nur das Prinzip verdeutlichen und da ich (bei der Wärme) leicht tippfaul bin, habe ich es mir einfach gemacht.

Das kannst Du am einfachsten mit einer Common Table Expression (CTE) umsetzen; da selektierst Du aus den einzelnen Tabellen eine Sicht, wie ich sie verwendet habe; das ist unten das erste Beispiel.

Es auf die einzelnen Tabellen hin aufzulösen, ich aber auch nicht wild; das ist dann das zweite Beispiel.

SET NOCOUNT ON;

 

CREATE TABLE #fm

    (id int ,film varchar(20));

INSERT INTO #fm VALUES (1, 'Titanic');

INSERT INTO #fm VALUES (2, 'Pulp Fiction');

GO

 

CREATE TABLE #ac

    (id int ,act varchar(20));

INSERT INTO #ac VALUES (1, 'Leonardo DiCaprio');

INSERT INTO #ac VALUES (2, 'Kate Winslet');

INSERT INTO #ac VALUES (3, 'Billy Zane');

INSERT INTO #ac VALUES (4, 'John Travolta');

INSERT INTO #ac VALUES (5, 'Samuel Jackson');

GO

 

CREATE TABLE #acfm

    (filmid int, actid int);

INSERT INTO #acfm VALUES(1, 1);

INSERT INTO #acfm VALUES(1, 2);

INSERT INTO #acfm VALUES(1, 3);

INSERT INTO #acfm VALUES(2, 4);

INSERT INTO #acfm VALUES(2, 5);

 

-- Mit CTE

WITH mv (film, act)

AS (SELECT film, act

    FROM #acfm

         INNER JOIN #fm

            ON #acfm.filmid = #fm.id

         INNER JOIN #ac

            ON #acfm.actid = #ac.id)

SELECT film

      ,CAST((SELECT (SELECT act + ','

                     FROM mv AS SUB

                     WHERE SUB.film = MAIN.film

                     FOR XML PATH (''), Type

                    ) AS [Text()]

            ) AS NVARCHAR(MAX)

           ) AS actlist

FROM (SELECT DISTINCT film

      FROM mv

     ) AS MAIN;

 

-- Mit einzelnen Tabellen

SELECT film

      ,CAST((SELECT (SELECT #ac.act + ','

                     FROM #acfm

                          INNER JOIN #ac

                             ON #acfm.actid = #ac.id

                     WHERE #acfm.filmid = #fm.id

                     FOR XML PATH (''), Type

                    ) AS [Text()]

            ) AS NVARCHAR(MAX)

           ) AS actlist

FROM #fm;

 

GO

DROP TABLE #acfm;

DROP TABLE #fm;

DROP TABLE #ac;

 

 

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 4

... super, das hilft mir sehr weiter! Eine allerletzte Frage hierzu - O.a. Statement liefert mir Ergebnisse wie folgt zurück:

Name         actlist
Titanic       <expr1>Leonardo DiCaprio</expr1><expr1>Kate Winslet</expr1><expr1>Billy Zane</expr1>
Titanic       NULL
Pulp Fiction     <expr1>John Travolta</expr1><expr1>Samuel Jackson</expr1>
Pulp Fiction     NULL

Wie ist das o.a. Statement zu erweitern, so dass die "NULL" Datensätze unterdrückt werden?

THX!

Answer 5

?? Also, das Statement von mir liefert keine NULL Werte; wie auch? Basis ist die #fm (Filme-) Tabelle, da gibt es nur 2 Einträge und somit auch nur 2 Ergebniszeilen.

Das wird an Deinen Daten / Deinem Statement liegen, die ich beide nicht kenne. Erst Du auch mal ein kleines Beispielscript mit Daten, damit wir hier es nachvollziehen können, wo das Problem liegt.

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 6

Ok sorry, ich hatte doppelte Einträge in meiner Movies-Tabelle ;-) Dein Skript funktioniert einwandfrei!

Answer 7

Hallo Olaf,

eine allerletzte Frage hierzu ;-) Wie ist das Select-Statement zu erweitern, so dass die Ergebnismenge wie folgt in ein Stammelement geschachtelt wird:

<actlist>
	<expr1>Leonardo DiCaprio</expr1><expr1>Kate Winslet</expr1><expr1>Billy Zane</expr1>
</actlist>