CODEWITHSUNDEEP
sqlstring-aggregation
shamp7598
shamp7598

Reputation: 39

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

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.

enter image description here

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.

Upvotes: 0

Views: 4140

Answers (2)

VBoka
VBoka

Reputation: 9083

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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

Upvotes: 1

Related Questions