Difference between revisions of "Python/SQL-GROUP CONCAT"
From Apache OpenOffice Wiki
< Python
Marcoagpinto (Talk | contribs) ([Python,Calc] Group_Concat for the spreadsheet) |
m |
||
| (One intermediate revision by one other user not shown) | |||
| Line 1: | Line 1: | ||
| − | |||
{{DISPLAYTITLE:Group_Concat for the spreadsheet}} | {{DISPLAYTITLE:Group_Concat for the spreadsheet}} | ||
| − | This script was published by Villeroy at the [ | + | This script was published by Villeroy at the [https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=59442 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). | A most simple draft for an SQL GROUP_CONCAT function as found in many database engines (e.g. HSQLDB 2.x and MySQL). | ||
| Line 10: | Line 9: | ||
== Original code == | == Original code == | ||
| − | < | + | <syntaxhighlight lang="Python"> |
import uno | import uno | ||
| Line 49: | Line 48: | ||
g_exportedScripts = makeGroupConcat_draft, | g_exportedScripts = makeGroupConcat_draft, | ||
| − | </ | + | </syntaxhighlight> |
== Process of the script == | == Process of the script == | ||
[[Category:Python]] | [[Category:Python]] | ||
Latest revision as of 13:59, 15 May 2021
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,
