How to concatenate rows into a single text string?

Question

This is my first question in StackOverflow and I would like to have little help from you.

I need a query that returns a string with all users' scores concatenated, grouped by their username. I'll try to exemplify:

username          score
John421             4028
Robin                  5392
Ninaww              2234
Robin                  4631
Robin                  2792
John421             8924

The query should return:

username          all_scores
John421            '4028,8924'
Robin                 '5392,4631,2792'
Ninaww             '2234'

Hope someone can help me and thanks in advance! :)

Welcome to Stack Overflow. You should take a look at [How to Ask](http://stackoverflow.com/questions/how-to-ask), as it is normally expected that people should put some work in themselves and explain in the question what they have done and how their efforts failed. Luckily people are feeling sorry for you today and you have some answers! — Borodin, May 27 '14 at 17:47

score 0 · Answer 1 · answered May 27 '14 at 15:20

Use oracle listagg() aggregate function, as described here:

http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

For example,

select username, listagg(score, ',') within group (order by score) as all_scores
from mytable
group by username

score 0 · Answer 2 · answered May 27 '14 at 15:47

The listagg function should be used here. One can use either the analytic or the group-set version of this function.

Since one wants to summarize the results by username, the group-set version appears to be the right one to choose.

-Identify the group (here it is username).

-Identify the delimeter, '; '.

-Identify the sorting of the scores. e.g. score)

SELECT username,
       LISTAGG(score, '; ') WITHIN GROUP (ORDER BY score) all_scores 
  FROM user_scores 
  GROUP BY username 
  ORDER BY username;

How to concatenate rows into a single text string?

2 Answers2