Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

MyGroupConcat: A MySQL UDF aggregate function for string concatenation

0.00/5 (No votes)
23 Mar 2004 1  
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:

    
    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?

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