SQL looping over rows

Question

I'd like to create a function in SQL that takes user input to generate a result like the table below with exercise names, then concatenate each row of the results table into a single string field.

FOR EXAMPLE

return this single string:

Bench, Pause Squat, Pull-ups, RDL, Shoulder Press

This is what i have so far. Please let me know if this is possible?

DELIMITER $$ 
CREATE FUNCTION get_exercises(workout_idV INTEGER, dateV DATE) RETURNS TEXT
BEGIN 
  SET @results = NULL;
  SET @names = NULL;
  
  /* @names becomes the single column table */
  SELECT DISTINCT(exercise_name) INTO @names
  FROM workout w 
  INNER JOIN `set` s ON s.workout_id = w.workout_id
  WHERE workout_id = workout_idV AND w.date = dateV;
  
  /* loop through @names and append each row to @results variable */
  
  RETURN(@results);
END $$
DELIMITER ;

score 0 · Accepted Answer · answered Nov 10 '22 at 21:28

It is already written

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

Does exactly what you want.

   SELECT 
     GROUP_CONCAT(DISTINCT exercise_name
                  ORDER BY exercise_name DESC SEPARATOR ', ')
   FROM workout 
   GROUP BY workout_id ;

SQL looping over rows

1 Answers1