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)

  • How can I insert the value of a variable in a throw statement?
  • Concatenate two columns and join by ID
  • powershell extract a value from a table
  • Exporting SharePoint usage log files into a database using LogParser
  • How to add a new schema to sql server 2008?
  • SET NOCOUNT ON and reading messages using C# and ADO.NET
  • ----------------------------
    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.