Sql subquery that converts a column into a list

November 9, 2021

Is it possible to write a subquery that will return the result in a string.

Code:

SELECT service_provider.service_provider_id,f_name,l_name,email,phone,service_provider.timestamp,categories.category 
FROM service_provider 
INNER JOIN service ON
   service.service_provider_id = service_provider.service_provider_id
INNER JOIN categories ON
   categories.category_id = service.category_id
where service_provider.status = 'active'

I have tried above query it return results in separate lines, what I want is when the value in category column is different it should add up in column as a list.

>Solution :

Use GROUP_CONCAT() to concatenate all the category names.

SELECT service_provider.service_provider_id,f_name,l_name,email,phone,service_provider.timestamp,GROUP_CONCAT(c.category) AS categories
FROM service_provider 
INNER JOIN service ON
   service.service_provider_id = service_provider.service_provider_id
INNER JOIN categories ON
   categories.category_id = service.category_id
where service_provider.status = 'active'
GROUP BY service_provider.service_provider_id