0

How do i combine rows of similar results into one? For example this is what i have: 

Title | Actor | StartYear | Genres
Noah | Adam   | 2000 | Documentary
Noah | Eve    | 2000 | Documentary
Noah | Joshua | 2000 | Documentary
Narnia | Peter | 2005 | Action
Narnia | James | 2005 | Action

What i want : 

Title | Actor | StartYear | Genres
Noah | Adam, Eve, Joshua  | 2000 | Documentary
Narnia | Peter, James | 2005 | Action

The method provided in this link which is STRING_AGG doesn't work because what i have 10 columns. (or am i wrong? i cant execute the query if i used STRING_AGG because the other columns are not in the grouped by clause) Comma separated results in SQL

For example, this works,

 SELECT primaryTitle, STRING_AGG(Actor, ', ') FROM table GROUP BY primaryTitle

This does not

SELECT primaryTitle, STRING_AGG(Actor, ', '), StartYear, Genre FROM table GROUP BY primaryTitle
Cecil
  • 13
  • 5
  • Did you get an error when you tried the second query? What was the error message? – Tab Alleman Jun 13 '19 at 14:37
  • so you did not get an answer in your same question [here](https://stackoverflow.com/questions/56580876/how-combine-rows-of-separate-results-into-one) ? – GuidoG Jun 13 '19 at 14:50
  • Possible duplicate of [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) – GuidoG Jun 13 '19 at 14:51

1 Answers1

2

You just need to be sure that you are grouping by all the columns you are not applying an aggregate function to. i.e.

SELECT primaryTitle, STRING_AGG(Actor, ', '), StartYear, Genre 
FROM table 
GROUP BY primaryTitle, StartYear, Genre
strickt01
  • 3,959
  • 1
  • 17
  • 32