Rolling up multiple rows into single row using 2 tables

Is there any way to change a SQL query that would normally return multiple rows with the same values into a single row as comma separated?

Table1
------
Col1
------
Sci-Fi
Action
Crime

Table2
------------
Col1 | Col2
------------
1    | Action
1    | Sci-Fi
2    | Crime
2    | Action
2    | Sci-Fi

And I need a query that results like this:
(Table1 and Table2 combined)

  • Sql comparaison nested on one column
  • SQL Server 2008 : What is the best way for inserting big chunk of data?
  • SQL Server multiple tables query for a specific date from many
  • How can I connect to an external database from a sql statement or a stored procedure?
  • generate where clause dynamically with dynamic column names
  • Use Invoke-SQLCmd and return number of rows deleted/affected
  • ----------------------------
    Col1 |  Col2
    ----------------------------
    1    | Action, Sci-Fi
    2    | Crime, Action, Sci-Fi
    

    2 Solutions collect form web for “Rolling up multiple rows into single row using 2 tables”

    SELECT MG.movie_id
    , STUFF((
    SELECT ',' + G.genre_name
    FROM Movie_Genre AS G
    WHERE G.movie_id = MG.movie_id
    ORDER BY G.genre_name FOR XML PATH('') ), 1, 1, '') AS Genres
    FROM Movie_Genre AS MG
    GROUP BY MG.movie_id

    Credit to this post for the crazy awesome STUFF expression.

    You can use STUFF function to combine multiple row as comma separated.

    Sample SQl Fiddle

    SELECT ID, col2 =
                stuff((
                       SELECT ','+ [col2] FROM t WHERE Id = t1.Id FOR XML PATH('')
                      ),1,1,'') 
    FROM (SELECT DISTINCT ID FROM t ) t1
    

    Refer Here for More

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.