This week I had to do something new in SQL. I needed to take a list of multiple route records for each driver and append the routes into single string for that driver. This is relatively simple in a Crystal report, but not so simple in a SQL Query. After I realized how complex this would be in a normal SQL query I searched to see if there was a simpler way. What I found was that several databases have a function specifically designed for this task.
In SQL Server and Postgre SQL it is called String_Agg().
In Oracle and DB2 it is called ListAgg().
In MySQL it is called Group_Concat().
Even though SQL is considered a “standard” language, some function names and syntax will vary from one database to another. That is why I created a “cheat sheet” a few years ago that lists the most common SQL functions (with their correct syntax) for the 8 SQL flavors I encounter the most:
- SQL Server
- MS Access
Today I added these “aggregate” functions to the cheat sheet along with a few other updates. The cheat sheet is now also available on the library page of my web site.(For examples of my most popular formulas, please visit the FORMULAS page on my website.)