I often need string concatenation to behave just like an aggregate function.
Once again I find a need to do that in SQLite, and to do that without recompiling
SQLite for every platform we distribute for...

   1  
   2  #include <stdlib.h>
   3  #include "sqlite3ext.h"
   4  SQLITE_EXTENSION_INIT1
   5  
   6  
   7  typedef struct SCtx SCtx;
   8  struct SCtx {
   9    int rowCnt;
  10    int charCnt;
  11    char *result;
  12  };
  13  
  14  static void concat_step(sqlite3_context* ctx, int argc, sqlite3_value**argv) {
  15  
  16    SCtx *p = (SCtx *) sqlite3_aggregate_context(ctx, sizeof(*p));
  17  
  18    char *sep = sqlite3_value_text(argv[1]);
  19  
  20    char *txt = sqlite3_value_text(argv[0]);
  21  
  22    if (p->rowCnt) {
  23      char *txt2 = malloc(strlen(txt) + strlen(sep) + 1);
  24      strcpy(txt2,sep);
  25      strcat(txt2,txt);
  26      txt = txt2;
  27    }
  28  
  29    //  printf("%d. Txt: [%s] len %d\n", p->rowCnt, txt, strlen(txt));
  30  
  31    int len = strlen(txt);
  32  
  33    if (!p->result) {
  34      p->result = malloc(len + 1);
  35      strcpy(p->result, txt);
  36    } else {
  37      p->result = realloc(p->result, strlen(p->result) + len + 1);
  38      strcat(p->result,txt);
  39    }
  40    //  printf ("intermediate [%s]\n", p->result);
  41    p->rowCnt++;
  42  }
  43  
  44  static void concat_final(sqlite3_context* ctx,
  45                           int argc,
  46                           sqlite3_value** argv) {
  47  
  48    SCtx *p = (SCtx *) sqlite3_aggregate_context(ctx, sizeof(*p));
  49    //  printf("Finally: %s\n", p->result);
  50    sqlite3_result_text(ctx,  p->result, strlen(p->result), NULL);
  51  }
  52  
  53  int sqlite3_extension_init(
  54    sqlite3 *db,
  55    char **pzErrMsg,
  56    const sqlite3_api_routines *pApi
  57  ){
  58    SQLITE_EXTENSION_INIT2(pApi)
  59    sqlite3_create_function(db, "concat", 2, SQLITE_ANY, 0, NULL, concat_step, concat_final);
  60    return 0;
  61  }

I compiled this with the following (here, ./src is the SQLite code - I used
http://www.sqlite.org/sqlite-source-3_3_13.zip).

   1  
   2  gcc -fpic -c agg.c -I./src
   3  gcc -shared -Wl,-soname,libagg.so -o libagg.so agg.o

And here's how it works:

   1  
   2  sqlite> CREATE TABLE test (animals VARCHAR, interjection VARCHAR);
   3  
   4  sqlite> insert into test (animals, interjection) values ('lions', 'oh my');
   5  
   6  sqlite> insert into test (animals, interjection) values ('tigers', 'oh my'); 
   7  
   8  sqlite> insert into test (animals, interjection) values ('bears', 'oh my');
   9  
  10  sqlite> select load_extension('./libagg.so');
  11  
  12  sqlite> select concat(animals, ' and '), interjection from test group by interjection;
  13  
  14  sqlite> lions and tigers and bears|oh my

See also:
1. http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions
2. http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
3. http://www.sqlite.org/capi3ref.html