How to do pivot table without knowledge of columns

Question

I have read most of the posts on stackoverflow on how to do a pivot table but all of the posts show examples with prior knowledge of the columns. How do you construct a query if you have no knowledge of what the columns will be. here is some sample data:

id       column       value       Row
1        age          13          1
2        height       55          1
3        fav number   NULL        1
4        siblings     4           1
5        age          55          2
6        height       54          2
7        fav number   12          2

I am looking for this output:

row        age       height        fav number       siblings
1          13        55            NULL             4
2          55        54            12               NULL

As you can see there is no row 2 is missing an entry for siblings. The column names are unknown at the time of the query. How would you make this query.

Answer 1

I don't see any way you can just write some fancy SELECT query to get what you want. You're going to have to do some pre-processing.

---

You have to be executing this MySQL query from some sort of program, application, script, etc. Not sure what the language is, but here's what I would do in PHP:

/* $data is where our data is going to be stored in our desired format */
$data = array();
/* $columns is a list of all column names */
$columns = array();
/* $rows is a list of all row names (probably '1', '2', etc) */
$rows = array();

$result = mysql_query('SELECT column, value, row FROM TableName');
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  /* if this row isn't in $data yet, add it */
  if (!array_key_exists($row['row'], $data) {
    $data[$row['row']] = array();
  }

  /* if this column isn't in $columns yet, add it */
  if (!in_array($row['column'], $columns)) {
    array_push($columns, $row['column']);
  }

  /* if this row isn't in $rows yet, add it */
  if (!in_array($row['row'], $rows)) {
    array_push($rows, $row['row']);
  }

  /* set the actual value in our multi-dimensional array $data */
  $data[$row['row']][$row['column']] = $row['value'];
}
/* free the result (php specific thing) */
mysql_free_result($result);

/* if we didn't set anything (row, column) pairs, set it to null in $data */
foreach ($rows as $r) {
  foreach ($columns as $c) {
    if (!array_key_exists($c, $data[$r])) {
      $data[$r][$c] = null;
    }
  }
}

This will put all the data into a format you want in an array in PHP.

---

For example, after running this algorithm on the sample data you provided above, you would be able to do:

echo $data['2']['age']; // $data['row']['column']

Which would output 55.

---

OR if your database isn't being updated in real time (you have a bunch of data that you'd like to reformat once, rather than continuously), you could extend the script above to also have some "CREATE TABLE", "INSERT INTO" queries that basically recreate the table in the format you're looking for.

Furthermore, if you ARE receiving data in realtime, you can still write the script described above, but you'd just want to remove the rows from the original table as you processed them, and then just run the script whenever data is being put into the original table.

Answer 2

I doubt that you can do this in MySQL or PostgreSQL as you expect to, however there is an alternative which I have used where data is very free-form. Our use case is "attributes that the menu items pass back to the application" and of course we have no knowledge of these in the query. But you can't create a simple pivot table.

The reason you can't is that PostgreSQL requires that the tuple structure returned to be defined in advance. Not all db's do this (Informix for example, allows different rows to have different structures!) but most do.

Our approach is PostgreSQL-only. However, maybe with some tweaking you can find a MySQL equivalent somewhere. What we did would basically be in your version:

select row, array_agg("column" || '=' || "value") from sample_data group by row;

This produces output like:

1 {"age=3","height=55",null,"siblings=4"}
2 {"age=55","height=54","favorite_number=12"}

You can even get rid of the NULLS by:

select row, array_agg("column" || '=' || "value")
 WHERE value is not null
 GROUP BY row;

Then you get something like:

1 {"age=3","height=55","siblings=4"}
2 {"age=55","height=54","favorite_number=12"}

I don't know how to do the same in MySQL though.