Extract matching data from varying number of row

Question

I am trying to build graphic indicators from a (potentially) large set of data using google-spreadsheet.

So far, I've used a pivot table to extract the information from the raw data, and I want to build intermediate tables to calculate the different values I need for my indicators.

So far, my pivot table looks as follow :

                     Status1 | Status2 | Status3 | Status4
                     -------------------------------------
Country1 | Domain1 |    1    |    2    |   1     |   
           Domain2 |         |    2    |         |   
----------------------------------------------------------
Country2 | Domain1 |         |         |         |   1
----------------------------------------------------------
Country3 | Domain2 |         |    1    |         |   3
           Domain3 |         |         |   1     |   

And I would like to generate the following table (there is a fixed number of Status & Domain) :

         Status1 | Status2 | Status3 | Status4 | Domain1 | Domain2 | Domain3
         -------------------------------------------------------------------
Country1 |    1  |    4    |   1     |    0    |    4    |   2     |   0
----------------------------------------------------------------------------
Country2 |    0  |    0    |   0     |    1    |    1    |   0     |   0
----------------------------------------------------------------------------
Country3 |    0  |    1    |   1     |    3    |    0    |   4     |   1

As the number of country and the number of rows present in the pivot table for each country will vary, I am unsure as to how to generate this table.

So far I was able to generate the 1st column using the following formula : UNIQUE(FILTER('myRange',NOT(ISBLANK('myRange')))), but I don't know how to proceed afterward. Is there a way to dynamically extract such data ? Should I change my pivot table ? Maybe build a pivot table using my 1st pivot table ?

Answer 1

I think this is possible using a formula:

={QUERY(A2:D,"select A,count(D) where A <> '' group by A pivot C"),QUERY(A2:D,"select count(D) where A <> ''group by A pivot B")}

Here is an example sheet (the formula is in cell F1): https://goo.gl/T9xI1v