0

I have 3 tables:

MovieDB

movieID | Title
1       | Star War

StarringDB

movie ID | nameID
1        | 1
1        | 2
1        | 3
1        | 4
1        | 5

nameDB

nameID | Name
1      | Harrison Ford
2      | Mark Hamill
3      | Carrie Fisher
4      | Peter Cushing
5      | Alec Guinness

I'd like the output to be:

ID | Title    | Starrings
1  | Star War | Harrison Ford, Mark Hamill, Carrie Fisher, Peter Cushing, Alec Guinness
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mike
  • 1
  • 1
  • 1
  • 1
    What database are you using? What have you tried? – sybkar Apr 11 '13 at 18:49
  • @sybkar - Have you read the title? – PM 77-1 Apr 11 '13 at 18:51
  • @PM77-1 - I did read the title, and even though I was pretty sure he meant MS SQL Server, SQL:2008 is also a SQL standard - since Mike is a new user, getting him started on the right track seems prudent – sybkar Apr 11 '13 at 18:54
  • 1
    Since SQL Server does not have a direct analogue of MySql's GROUP_CONCAT you will need to use a work-around posted on the web. See http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server, http://stackoverflow.com/questions/12997884/sql-server-group-by-string-concatenation, and similar. – PM 77-1 Apr 11 '13 at 18:55

1 Answers1

1

SQL Server doesn't have a function that will concatenate the value for you so you can use FOR XML PATH to get the result:

select m.movieID,
  m.title,
  STUFF((SELECT  ', ' + n.name
         from name n
         inner join starring s
           on n.nameID = s.nameid
         where s.movieID = m.movieID
         group by n.name, n.nameid
         order by n.nameid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') starrings
from movie m;

See SQL Fiddle with Demo.

Or you can use FOR XML PATH and CROSS APPLY:

select m.movieID,
  m.title,
  left(c.stars, len(c.stars)-1) stars
from movie m
cross apply
(
  select n.name + ', '
  from name n
  inner join starring s
    on n.nameID = s.nameid
  where s.movieID = m.movieID
  group by n.name, n.nameid
  order by n.nameid
  FOR XML PATH('')
) c (stars);

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Nice, I never noticed the column_alias bit on `derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] ` – Conrad Frix Apr 11 '13 at 19:50