How to take multiple rows and make a comma separated list in SQL

Question

I have a query I ran which is

SELECT * FROM rpg.class_primary_abilities AS cpab INNER JOIN rpg.abilities AS ab ON cpab.ability_id = ab.ability_id INNER JOIN rpg.classes AS cl ON cpab.class_id = cl.class_id;

It gives me the below output.

What I would like to know is how can I change this query in order to display everything except the first class_id column in a comma separated list. Essentially I would like to get an output of class_idand value,value,value,value,value,value,value,value as my two columns in the output.

I am a college student just learning SQL and have not been exposed to any possible solutions to this problem in class. Any help is appreciated.

Tag your question with the database you are using. Show the results that you want. — Gordon Linoff, Feb 12 '20 at 15:14
Yes, it depends on the database, so you should say which database are you using, although the UI style appears to be SQL Server for me. — xecollons, Feb 12 '20 at 16:15
If you want SQL then look at these: [stack overflow post #1](https://stackoverflow.com/questions/887628/convert-multiple-rows-into-one-with-comma-as-separator) [stack overflow post #2](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — Chris, Dec 06 '21 at 19:56

score 1 · Answer 1 · answered Feb 12 '20 at 15:16

Most databases support a function such as string_agg() or listagg() that does what you want:

SELECT cl.class_id,
       STRING_AGG(ability_id, ',') as ability_ids,
       . . . 
FROM rpg.class_primary_abilities cpab JOIN
     rpg.abilities ab
     ON cpab.ability_id = ab.ability_id JOIN
     rpg.classes cl
     ON cpab.class_id = cl.class_id
GROUP BY cl.class_id

VBoka · Accepted Answer · 2020-02-12T15:31:43.060

Please tag your database for more info.

This is one way for Oracle where Ihave used a concatenate sign which is || to concatenate(put two strings together in one) and between them I have also concatenated a comma ,. You can also see that I have used double quotes for the column named desc. I did it because it is not a good practice to call your columns with keywords and word desc is used for example when you order by some column(at the end of the query) you can order by that column ascending then you use asc or descending when you can use desc. Also in both examples I used keyword as to give a name to this concatenated column.

SELECT class_id, cpab.ability_id || ',' || 
                 ab.ability_id  || ',' || 
                 ab.name || ',' || 
                 class_id || ',' ||  
                 cpab.name || ',' ||  
                 hit_die || ',' || 
                 "desc" || ',' || 
                 isPlayable as values
FROM rpg.class_primary_abilities AS cpab
INNER JOIN rpg.abilities AS ab ON cpab.ability_id = ab.ability_id
INNER JOIN rpg.classes AS cl ON cpab.class_id = cl.class_id;

This is another for MYSQL where I have used concat to concatenate column values and I have used different single quotes for desc column.:

SELECT class_id, concat(cpab.ability_id, ',' , 
                        ab.ability_id, ',' ,
                        ab.name, ',' , 
                        class_id, ',' , 
                        cpab.name, ',' , 
                        hit_die, ',' , 
                        `desc`, ',' , 
                        isPlayable) as values
FROM rpg.class_primary_abilities AS cpab
INNER JOIN rpg.abilities AS ab ON cpab.ability_id = ab.ability_id
INNER JOIN rpg.classes AS cl ON cpab.class_id = cl.class_id;

In both examples you have columns with same name from different tables and theer you will have to use aliases when calling them in your select clause like I have did in my example: cpab.ability_id and ab.ability_id but please note that I do not know if they are from cpab and ab tables.

How to take multiple rows and make a comma separated list in SQL

2 Answers2