How to query in such a way that values becomes columns? [duplicate]

Question

Having the table foo like:

| id | name |
|  1 | Foo1 |
|  2 | Foo2 |

And the table bar like:

| id | foo_id | key | value |
|  1 |      1 |   a | foo1a |
|  2 |      1 |   b | foo1b |
|  3 |      2 |   a | foo2a |
|  4 |      2 |   b | foo2b |

How can I mount my SQL in a way that produces an output like:

| id | name |   a   |   b   |
|  1 | foo1 | foo1a | foo1b |
|  2 | foo2 | foo2a | foo2b |

I have no idea even if it is possible.

On a high-level problem (maybe there is a better solution) I want to make the keys on a entity dynamic.

Probably want to research mysql pivot and basically create a case statement for each of your desired columns and group by the foo_id — jwize, Jul 1, 2019 at 19:51

Kirk Saunders · Accepted Answer · 2019-07-01 19:48:54Z

1

I think what you are looking for is called a Pivot. I work primarily with Microsoft SQL Server, so I don't know all the inter-workings of this.

There appears to be a really well received Stack Overflow post that answers a similar question for MySQL.

Hopefully that give you what you are looking for.

answered Jul 1, 2019 at 19:48

Kirk Saunders

1,0316 silver badges19 bronze badges

Thank you very much! The keyword (pivot) makes a huge difference, I know how to search now.
– Leo Cavalcante
Jul 2, 2019 at 20:09

Add a comment |

1 Answer 1