Click here to Skip to main content
Click here to Skip to main content
Go to top

MyGroupConcat: A MySQL UDF aggregate function for string concatenation

, 23 Mar 2004
Rate this:
Please Sign up or sign in to vote.
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, ' ')
    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:

    <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:

    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

    <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:

    <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:

    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):

    <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'):

    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?

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

Share

About the Author

Emmanuel Kartmann
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 Pinsusstamtaly15-Sep-05 21:24 
QuestionWhere to put the dll file? Pinsussmiliu5-Dec-03 6:05 
QuestionSQL Query?? select tabel as alias? PinsussAnonymous16-Sep-03 8:03 
GeneralThis function is available for MySQL 4.1 PinmemberEmmanuel Kartmann7-Jul-03 21:35 
GeneralMore details PinmemberJonathan de Halleux22-Jun-03 21:26 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140922.1 | Last Updated 24 Mar 2004
Article Copyright 2003 by Emmanuel Kartmann
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid