Rolling up multiple rows into single row using 2 tables

Question

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)

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

I don't understand - what does the first table have to do with the second? Why is it even needed? — Siyual, Jun 02 '15 at 18:54
use PIVOT https://technet.microsoft.com/en-gb/library/ms177410(v=sql.105).aspx — Rocketq, Jun 02 '15 at 18:55
Duplicate: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Evaldas Buinauskas, Jun 02 '15 at 18:56
Table1 contains much more data like: genre_description etc. I can use this 'Table1' in the future to add genre's to 'new' movies — STPHAN, Jun 02 '15 at 18:57
Look into using the STUFF command in conjunction with FOR XML. Plenty of examples online. — William Xifaras, Jun 02 '15 at 18:59

score 3 · Accepted Answer · edited May 23 '17 at 12:05

3

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.

edited May 23 '17 at 12:05

Community

1
1

answered Jun 02 '15 at 19:09

MartianCodeHound

355
1
8

I'm getting this error: Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'Action' to data type int. – STPHAN Jun 02 '15 at 19:25
Ah, your WHERE G.genre_name = MG.movie_id is not going to work because you are comparing an int to a varchar. You don't need table 1 at all. Only table 2 should be referenced and change the where clause to WHERE G.movie_id = NG.movie_id – MartianCodeHound Jun 02 '15 at 19:43
Fixed your query: 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 – MartianCodeHound Jun 02 '15 at 20:04
TY user1499246, could you edit you're answer to this query? I accept when you do it. – STPHAN Jun 02 '15 at 20:06

score 1 · Answer 2 · edited May 23 '17 at 11:51

1

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

edited May 23 '17 at 11:51

Community

1
1

answered Jun 02 '15 at 19:11

HaveNoDisplayName

8,291
106
37
47

I'm getting this error: Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'Action' to data type int. – STPHAN Jun 02 '15 at 19:24
SELECT MG.movie_id, STUFF(( SELECT ',' + G.genre_name FROM Genre AS G WHERE G.genre_name = 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 – STPHAN Jun 02 '15 at 19:34
which table is Genre Or Movie_Genre in your Question? – HaveNoDisplayName Jun 02 '15 at 19:44

Rolling up multiple rows into single row using 2 tables

2 Answers2

Linked