Click here to Skip to main content
15,868,164 members
Articles / Database Development / MySQL
Article

MyGroupConcat: A MySQL UDF aggregate function for string concatenation

Rate me:
Please Sign up or sign in to vote.
3.47/5 (11 votes)
23 Mar 20043 min read 87.7K   680   17   5
This MySQL extension library provides an aggregate function that concatenate strings (for use with SELECT...GROUP BY clause).

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, ' ')<BR>FROM users<BR>GROUP BY id<BR>;

SAMPLE CODE (SQL)

Create test data

    To create and fill the test table test_group_concat, use the following SQL script:

    SQL
    <TT>
    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
    ;
          </TT>

    You will get the following output:

    idcodelabel
    1A010LINE 1 LABEL
    2A011LINE 2 LABEL
    3A0111LINE 3 LABEL
    4A020LINE 1 LABEL 2
    5A021LINE 2 LABEL 2
    6B010LINE 1 LABEL 3
    7B011LINE 2 LABEL 3
    8B0111LINE 3 LABEL 3
    9B0111LINE 4 LABEL 3
    10B0111LINE 5 LABEL 3
    11B0111LINE 6 LABEL 3
    12B020LINE 1 LABEL 4 (nulls)
    13B021
    14B0211
    15B0211LINE 3 LABEL 4 (nulls)
    16B0211LINE 5 LABEL 4 (nulls)
    17B0211LINE 6 LABEL 4 (nulls)

Display group_concat() output only

    SQL
    <TT>
    SELECT
      group_concat(label) AS label
    FROM
      test_group_concat
    GROUP BY
      LEFT(code, 3)
    ;
          </TT>

    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:

    SQL
    <TT>
    SELECT
      LEFT(code, 3) AS code, group_concat(label) AS label
    FROM
      test_group_concat
    GROUP BY
      LEFT(code, 3)
    ;
          </TT>

    You will get the following output:

    codelabel
    A010LINE 1 LABELLINE 2 LABELLINE 3 LABEL
    A020LINE 1 LABEL 2LINE 2 LABEL 2
    B010LINE 1 LABEL 3LINE 2 LABEL 3LINE 3 LABEL 3LINE 4 LABEL 3LINE 5 LABEL 3LINE 6 LABEL 3
    B020LINE 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):

    SQL
    <TT>
    SELECT
      LEFT(code, 3) AS code, group_concat(label, ' ') AS label
    FROM
      test_group_concat
    GROUP BY
      LEFT(code, 3)
    ;
          </TT>

    In that case, the separator is concatenated for every field value found (unless field value IS NULL like for code 'B02'):

    codelabel
    A010LINE 1 LABEL LINE 2 LABEL LINE 3 LABEL
    A020LINE 1 LABEL 2 LINE 2 LABEL 2
    B010LINE 1 LABEL 3 LINE 2 LABEL 3 LINE 3 LABEL 3 LINE 4 LABEL 3 LINE 5 LABEL 3 LINE 6 LABEL 3
    B020LINE 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?

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
France France
Fell into computer software at the age of 11, founder of 3 startups, and now manager of an independent software vendor (ISV) labelled proSDK (www.prosdk.com)... And still a freeware writer and technical article author!

Comments and Discussions

 
GeneralString Concatenation in SQL 2000 Pin
tamtaly15-Sep-05 21:24
tamtaly15-Sep-05 21:24 
QuestionWhere to put the dll file? Pin
Ming Liu5-Dec-03 6:05
Ming Liu5-Dec-03 6:05 
QuestionSQL Query?? select tabel as alias? Pin
Anonymous16-Sep-03 8:03
Anonymous16-Sep-03 8:03 
GeneralThis function is available for MySQL 4.1 Pin
Emmanuel Kartmann7-Jul-03 21:35
Emmanuel Kartmann7-Jul-03 21:35 
GeneralMore details Pin
Jonathan de Halleux22-Jun-03 21:26
Jonathan de Halleux22-Jun-03 21:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.