Xaprb

MySQL 4.1 adds a number of new aggregate functions, among them GROUP_CONCAT. Earlier versions require you to build and compile the function as a C++ extension, which I’m told is easy. However, that is usually not possible in a shared hosting environment.

It is possible to use user variables to write some queries without GROUP_CONCAT. The key is to understand how MySQL implements selects. As the following example shows, it really loops through each row one at a time, evaluating the expression for each. This example uses the same fruits table as in my article on simulating the ROW_NUMBER function:

set @result = '';
select @result := concat(@result, variety, ' ') 
from fruits where type = 'apple';
+------------------------------------------+
| @result := concat(@result, variety, ' ') |
+------------------------------------------+
| fuji                                     |
| fuji gala                                |
| fuji gala limbertwig                     |
+------------------------------------------+
select @result;
+-----------------------+
| @result               |
+-----------------------+
| fuji gala limbertwig  |
+-----------------------+

It is possible to get similar functionality from Microsoft SQL Server 2000 with a local variable.

declare @result varchar(8000);
set @result = '';
select @result = @result + name + ' '
    from master.dbo.systypes;
select rtrim(@result);

The result is the string “image text uniqueidentifier tinyint smallint int smalldatetime real money datetime float sql_variant ntext bit decimal numeric smallmoney bigint varbinary varchar binary char timestamp nvarchar nchar sysname.” It is necessary to initialize the string to ” before the select, because NULL concatenates to NULL.

You might also like:

1. How to simulate the SQL ROW_NUMBER function
2. How to number rows in MySQL
3. How to select the first or last row per group in SQL
4. How to simulate FULL OUTER JOIN in MySQL
5. How to understand SQL joins