MyGroupConcat: A MySQL UDF aggregate function for string concatenation

• Download Article and Source Code (65 KB).
• Download self-extracting setup (691 KB).

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

You can concatenate fields (e.g. label) whose code starts with the same 3 letters:

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?