Using STRING_AGG functionality in Firebolt

Help Center
1

In Firebolt, if you're seeking to merge values from multiple rows into a single string (similar to the STRING_AGG function) you can use these two functions instead:

  • ARRAY_AGG (Explore this function's documentation here)
  • ARRAY_JOIN (Explore this function's documentation here)

Imagine you have a table named student_classes with fields like Student_name and Course_number:

image.png

Now, let's consider this SQL query to find out which courses each student is enrolled in:

SELECT Student_name, GROUP_CONCAT(Course_number) as Course_numberFROM student_classesGROUP BY Student_name;

To get the following results:

image.png

In Firebolt, you can achieve the same outcome with:

SELECT Student_name, ARRAY_JOIN(ARRAY_AGG(Course_number),', ') AS Course_numberFROM student_classesGROUP BY Student_name;

Here, ARRAY_AGG gathers the course numbers into an array, and ARRAY_JOIN brings them together into a single string. It's a bit like assembling a list and then joining the pieces together!