MyGroupConcat v2.1.1

Author: Emmanuel KARTMANN.

Last Update: January 7th, 2004

This MySQL extension library provides an aggregate function that concatenate strings (for use with SELECT...GROUP BY clause).

CONTENTS

• COMPONENT FEATURES
• USAGE
• SAMPLE CODE (SQL)
  • Create test data
  • Display group_concat() output only
  • Display group_concat() and value of GROUP BY clause
  • Display group_concat(), value of GROUP BY clause, and use separator
  • Display group_concat(), value of GROUP BY clause, use separator and start from given count
  • Display group_concat(), value of GROUP BY clause, use separator, start from given count and end before given count
• IMPLEMENTATION
• TO DO LIST

• 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()

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
;

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 4 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 4 LABEL 4 (nulls)
16	B0211	LINE 5 LABEL 4 (nulls)
17	B0211	LINE 6 LABEL 4 (nulls)

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 4 LABEL 4 (nulls)LINE 5 LABEL 4 (nulls)LINE 6 LABEL 4 (nulls)

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 4 LABEL 4 (nulls)LINE 5 LABEL 4 (nulls)LINE 6 LABEL 4 (nulls)

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 4 LABEL 4 (nulls) LINE 5 LABEL 4 (nulls) LINE 6 LABEL 4 (nulls)

You can also use a start count for concatenation (i.e. concat strings 3 or above in example below):

SELECT
  LEFT(code, 3) AS code, group_concat(label, ' ', 3) AS label
FROM
  test_group_concat
GROUP BY
  LEFT(code, 3)
;
      

In that case, only 3rd string and after are concatenated:


code	label
A01	LINE 3 LABEL
A02
B01	LINE 3 LABEL 3LINE 4 LABEL 3LINE 5 LABEL 3LINE 6 LABEL 3
B02	LINE 4 LABEL 4 (nulls)LINE 5 LABEL 4 (nulls)LINE 6 LABEL 4 (nulls)

Finally, you use an end count for concatenation (i.e. concat strings before 4th in example below):

SELECT
  LEFT(code, 3) AS code, group_concat(label, ' ', 1, 4) AS label
FROM
  test_group_concat
GROUP BY
  LEFT(code, 3)
;
      

In that case, only strings up to 4th (not included) are concatenated:


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 3
B02	0LINE 1 LABEL 4 (nulls)

• Implementation is based on MySQL sample code (source distribution)

• Provides aggregate version of other string functions?

Download

Download Article and Source Code (65 KB).

Download self-extracting setup (691 KB).