MySQL group multiple(variable) rows into one row with multiple columns

Question

I'm looking for a query that will return me several rows into columns but without knowing the number of rows beforehand. I have searched and the only solutions I found involve knowing how many rows there are.

Here's an example table:

parentID    colA       colB
2           aaaaaa     1000.00
2           bbbbbb     1500.00
3           cccccc     500.00
3           dddddd     700.00
3           eeeeee     2000.00

and i need it to look like:

parentID    colA(n)      colB(n)     colA(n+1)     colB(n+1)     colA(n+2)     colB(n+2)
2           aaaaaaa      1000.00     bbbbbb        1500.00       NULL          NULL
3           cccccc       500.00      dddddd        700.00        eeeeee        2000.00

I realize this should be done in PHP but I need it to be in mysql for a third party excel exporter plugin I'm using.

Edit: Is there a way to do this if I know the maximum number of columns I'll need?

Answer 1

You cannot do a query in SQL without knowing the number of columns.

The columns of a SELECT-list must be fixed at the time of parsing the query. What you're asking for is that the state of data, which is not known until the query executes, determines the number of columns. That is not the way SQL works.

To accomplish a pivot-type operation, or any query where the data determines the columns, you have two choices:

• Do a preparatory query to discover how many distinct groups you want to fetch, and use this to build the query with a matching number of columns.

• Query all the data in rows, fetch it back into your application, and then transform the result set wholly within data structures (i.e. arrays) within your code.

Either way, you need to write application code, either before or after fetching the data.

---

Re your comment: You're right, this isn't a traditional pivot, but it's similar in that data is driving the number of columns. That is, you need as many columns as 2x the number of rows in the largest group. But they won't all be filled, because the number of rows per group varies. You don't have a fixed number of row per group, therefore you can't fill a fixed number of columns per group.

I'd really recommend you use the latter strategy: fetch the data as rows, as they are stored in the database. Then post-process it in your application code. Loop over the result set and build your data structure incrementally as you fetch rows from the database.

For example, in PHP:

while ($row = $stmt->fetch()) {
  $data[$row['parentID']][] = $row['colA'];
  $data[$row['parentID']][] = $row['colB'];
}