Search code examples
sparqlwikidata

SPARQL - Concat values on multiple rows

I want a list of all currencies by El Salvador with their subdivision.

I use this query:

SELECT ?currency ?currencyLabel ?currencyIso4217 ?subdivisionLabel  {
  ?currency wdt:P498 ?currencyIso4217 .
  ?currency wdt:P9059 ?subdivision .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
  {
    ?country wdt:P38 ?currency .
    BIND(wd:Q792 AS ?country).
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
  }  
}

Try it here: Link

This gives the following result:

enter image description here

Works perfectly. But row 2 and three are the same currency. That currency only uses multiple names for the subdivision. I want them to concat so I get this output:

enter image description here

Is that possible?

Solution

  • Grouping in SPARQL works similar to SQL. The function GROUP BY combines results that have identical values into groups. Afterwards, an aggregate function is applied to the other (non-identical) values for each group.

    Typical aggregate functions are COUNT, SUM, MIN, MAX, AVG, GROUP_CONCAT, and SAMPLE.

    For you GROUP_CONCAT is of interest. It performs a string concatenation across the values of a group. With the argument separator you can even specify a separator character. The order of the strings is arbitrary.

    The syntax of GROUP BY and GROUP_CONCAT was already given to you in the comment by UninformedUser but I repeat it here in a slightly adapted form:

    SELECT ?currency ?currencyLabel ?currencyIso4217 (GROUP_CONCAT(?subdivisionLabel; separator = ", ") as ?subdivisionLabels) { 
  BIND(wd:Q792 AS ?country).
  ?country wdt:P38 ?currency .
  ?currency wdt:P498 ?currencyIso4217 .
  ?currency wdt:P9059 ?subdivision .
  ?subdivision rdfs:label ?subdivisionLabel .
  FILTER(lang(?subdivisionLabel) = 'en')
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } 
} GROUP BY ?currency ?currencyLabel ?currencyIso4217