OVERVIEW
This MySQL extension library provides an aggregate function that concatenate strings (for use with SELECT...GROUP BY clause).
COMPONENT FEATURES
This component:
- implements an aggregate function for string concatenation
- provides very small executable: 9 Kb
- runs on Windows XP and Windows 2000 Server
- compiles with VC++ 7.0 (Visual Studio .NET)
- Can be plugged into MySQL as a User-Defined Function (UDF) group_concat()
USAGE
To use this component from MySQL:
- create function
group_concat
:
CREATE AGGREGATE FUNCTION group_concat RETURNS STRING SONAME "MyGroupConcat.dll";
- call method group_concat() directly from SQL:
SELECT group_concat(first_name, ' ')
FROM users
GROUP BY id
;
SAMPLE CODE (SQL)
Create test data
To create and fill the test table test_group_concat
, use the following SQL script:
DROP TABLE IF EXISTS test_group_concat
;
CREATE TABLE test_group_concat(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(8),
label VARCHAR(255)
)
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'A01', '0LINE 1 LABEL')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'A011', 'LINE 2 LABEL')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'A0111', 'LINE 3 LABEL')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'A02', '0LINE 1 LABEL 2')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'A021', 'LINE 2 LABEL 2')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'B01', '0LINE 1 LABEL 3')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'B011', 'LINE 2 LABEL 3')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'B0111', 'LINE 3 LABEL 3')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'B0111', 'LINE 4 LABEL 3')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'B0111', 'LINE 5 LABEL 3')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'B0111', 'LINE 6 LABEL 3')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'B02', '0LINE 1 LABEL 4 (nulls)')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'B021', NULL)
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'B0211', NULL)
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'B0211', 'LINE 3 LABEL 4 (nulls)')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'B0211', 'LINE 5 LABEL 4 (nulls)')
;
INSERT INTO test_group_concat(id, code, label) VALUES(NULL, 'B0211', 'LINE 6 LABEL 4 (nulls)')
;
SELECT * FROM test_group_concat
;
You will get the following output:
id |
code |
label |
1 |
A01 |
0LINE 1 LABEL |
2 |
A011 |
LINE 2 LABEL |
3 |
A0111 |
LINE 3 LABEL |
4 |
A02 |
0LINE 1 LABEL 2 |
5 |
A021 |
LINE 2 LABEL 2 |
6 |
B01 |
0LINE 1 LABEL 3 |
7 |
B011 |
LINE 2 LABEL 3 |
8 |
B0111 |
LINE 3 LABEL 3 |
9 |
B0111 |
LINE 4 LABEL 3 |
10 |
B0111 |
LINE 5 LABEL 3 |
11 |
B0111 |
LINE 6 LABEL 3 |
12 |
B02 |
0LINE 1 LABEL 4 (nulls) |
13 |
B021 |
|
14 |
B0211 |
|
15 |
B0211 |
LINE 3 LABEL 4 (nulls) |
16 |
B0211 |
LINE 5 LABEL 4 (nulls) |
17 |
B0211 |
LINE 6 LABEL 4 (nulls) |
Display group_concat() output only
SELECT
group_concat(label) AS label
FROM
test_group_concat
GROUP BY
LEFT(code, 3)
;
You will get the following output:
label |
0LINE 1 LABELLINE 2 LABELLINE 3 LABEL |
0LINE 1 LABEL 2LINE 2 LABEL 2 |
0LINE 1 LABEL 3LINE 2 LABEL 3LINE 3 LABEL 3LINE 4 LABEL 3LINE 5 LABEL 3LINE 6 LABEL 3 |
0LINE 1 LABEL 4 (nulls)LINE 3 LABEL 4 (nulls)LINE 5 LABEL 4 (nulls)LINE 6 LABEL 4 (nulls) |
Display group_concat() and value of GROUP BY clause
SELECT
LEFT(code, 3) AS code, group_concat(label) AS label
FROM
test_group_concat
GROUP BY
LEFT(code, 3)
;
You will get the following output:
code |
label |
A01 |
0LINE 1 LABELLINE 2 LABELLINE 3 LABEL |
A02 |
0LINE 1 LABEL 2LINE 2 LABEL 2 |
B01 |
0LINE 1 LABEL 3LINE 2 LABEL 3LINE 3 LABEL 3LINE 4 LABEL 3LINE 5 LABEL 3LINE 6 LABEL 3 |
B02 |
0LINE 1 LABEL 4 (nulls)LINE 3 LABEL 4 (nulls)LINE 5 LABEL 4 (nulls)LINE 6 LABEL 4 (nulls) |
Display group_concat(), value of GROUP BY clause, and use separator
You can also use a specific separator (a space in example below) during the concatenation process (just like the CONCAT_WS function from MySQL):
SELECT
LEFT(code, 3) AS code, group_concat(label, ' ') AS label
FROM
test_group_concat
GROUP BY
LEFT(code, 3)
;
In that case, the separator is concatenated for every field value found (unless field value IS NULL like for code 'B02'):
code |
label |
A01 |
0LINE 1 LABEL LINE 2 LABEL LINE 3 LABEL |
A02 |
0LINE 1 LABEL 2 LINE 2 LABEL 2 |
B01 |
0LINE 1 LABEL 3 LINE 2 LABEL 3 LINE 3 LABEL 3 LINE 4 LABEL 3 LINE 5 LABEL 3 LINE 6 LABEL 3 |
B02 |
0LINE 1 LABEL 4 (nulls) LINE 3 LABEL 4 (nulls) LINE 5 LABEL 4 (nulls) LINE 6 LABEL 4 (nulls) |
IMPLEMENTATION
- Implementation is based on MySQL sample code (source distribution)
TO DO LIST
- Provides aggregate version of other string functions?