How to transform vertical data into horizontal data with SQL?

Regardless of the database you are using, the concept of what you are trying to achieve is called “Pivot Table”.

Here’s an example for mysql:

Some databases have builtin features for that, see the links below.



You can always create a pivot by hand. Just select all the aggregations in a result set and then select from that result set. Note, in your case, you can put all the names into one column using concat (i think that’s group_concat in mysql), since you cannot know how many names are related to a a rel_id.

pseudo-select for your case (i don’t know mysql):

select rel_id, group_concat(name) from item group by rel_id

Leave a Comment