CODEWITHSUNDEEP
sql-server
Cecil
Cecil

Reputation: 13

How to combine rows of similar results into one?

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

Upvotes: 0

Views: 65

Answers (1)

strickt01
strickt01

Reputation: 4048

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

Upvotes: 2

Related Questions