Make a pivot table?

Questions & Help Queries
1

Is it possible to make a query to compose a pivot table?

Suppose I have a block property ‘some-prop’ and want to have a table where first column is all possible values of this property and the second column is how many blocks have this value.

2

The output isn’t dynamic, but this query does what you describe.
This is for the property type. So change that to whatever property you need.

#+BEGIN_QUERY 
{:title [:h4 "Occurences per type"]
 :query [:find (pull ?b [*])
  :where
   (has-property ?b :type)
 ]
 :result-transform (fn [result]
  (map (fn [[key value]] {:k key :count (count value)})
    (group-by (fn [g] (get-in g [:block/properties :type])) result)
  )
 )
 :view (fn [rows] [:table 
  [:thead [:tr [:th "Type"] [:th "Occurences"] ]] 
  [:tbody (for [r rows] [:tr [:td (get r :k)] [:td (get r :count)] ])]
 ])
}
#+END_QUERY

image
image818×741 22.8 KB

3

Thanks! Now I’ve got to understand how it works :exploding_head:

‘Not dynamic’ means I have to re-trigger it somehow manually? Btw, what makes it not dynamic?

4

Not dynamic as a pivot table in Excel is :slight_smile:
It’s just a table with your result, nothing more.