Python/SQL-GROUP CONCAT

This script was published by Villeroy at the Code Snippets Forum.

A most simple draft for an SQL GROUP_CONCAT function as found in many database engines (e.g. HSQLDB 2.x and MySQL). Select a range of cells so the main category is found in the first column and the subcategories to be concatenated are found in the last column of the selection. Run the macro. The 2-column output will be dumped 2 rows below the selection.

Original code

import uno

def getGroupConcatDataArray(a):
   '''Get some data array, return a 2-column data array.'''
   sdelim = ','
   d = dict()
   for r in a:
      lst = d.get(r[0],list())
      s = r[-1]
      if not s in lst:
         lst.append(unicode(s))
      d[r[0]] = lst
   for (k,v) in d.iteritems():
      v.sort()
      d[k] = sdelim.join(v)
   return tuple(d.items())

def makeGroupConcat_draft(*args):
   '''Read from first and last column of selected range.
   Store first field as dictionary keys and concatenate the second.
   Dump the resulting dictionary 2 rows below current selection'''
   doc = XSCRIPTCONTEXT.getDocument()
   view = doc.getCurrentController()
   sel = view.getSelection()
   sh = sel.getSpreadsheet()
   a = sel.getDataArray()
   tgt = getGroupConcatDataArray(a)
   addr = sel.getRangeAddress()
   sc = addr.StartColumn
   ec = sc +1
   sr = addr.EndRow +2
   er = sr + len(tgt) -1
   rg = sh.getCellRangeByPosition(sc, sr, ec, er)
   rg.setDataArray(tgt)
   view.select(rg)

g_exportedScripts = makeGroupConcat_draft,