This shows how one can pivot a SELECT result set in SPARQL to have columns which correspond to row values. The topic is hot in the SQL world so perhaps one day people will start wondering about it in SPARQL.
This query pivots the (type, number_of_instances) relation generated by the inner subquery.
It uses Stardog-specific functions (:set and :index) but it's probably not too hard to adapt group_concat
(just a little clumsier, left as an exercise for the reader :)
prefix : <tag:stardog:api:>
# :index is a Stardog-specific function to access an array element by index
select (:index(?types, 0) as ?type0) (:index(?counts, 0) as ?count0)
(:index(?types, 1) as ?type1) (:index(?counts, 1) as ?count1)
(:index(?types, 2) as ?type2) (:index(?counts, 2) as ?count2)
{
{
# this is the Stardog-specific :set aggregate which collects values to an array
select (:set(?type) as ?types) (:set(?c) as ?counts) {
# this is the subquery which groups types and counts instances
select ?type (count(?s) as ?c) {
?s a ?type
}
group by ?type
order by ?type
}
}
}
The result looks like this:
+-------+--------+-------+--------+-------+--------+
| type0 | count0 | type1 | count1 | type2 | count2 |
+-------+--------+-------+--------+-------+--------+
| :X | 3 | :Y | 2 | :Z | 4 |
+-------+--------+-------+--------+-------+--------+
Actually after posting this I realised there're 2 issues with this query (namely, with
:set). It eliminates duplicates and it does not preserve the insertion order. In other words, it's a set (d'oh). So one'd need a similar:list(or:array) function. Probably 10mins job if you're familiar with custom functions in Stardog (or in your system of choice, provided it supports arrays in some form).