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: http://en.wikibooks.org/wiki/MySQL/Pivot_table

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

SQLServer: http://msdn.microsoft.com/de-de/library/ms177410.aspx

Oracle: http://www.dba-oracle.com/t_pivot_examples.htm

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

I think you are looking for a mysql specific answer. Keep in mind that the syntax could vary across different data stores.

MySQL has a feature that makes this easy.

SELECT Rel_ID, GROUP_CONCAT(Name SEPARATOR ' ') As Names FROM Item GROUP BY Rel_ID;

that should work :-)


if the names that you listed are static,my below query that i runned sucessfully in sqlfiddle will work

SELECT rel_id,
MAX (DECODE (rel_id, '1', DECODE (relrank, '1', name) , '2',DECODE (relrank, '1', name))) NAME1,
MAX (DECODE (rel_id, '1', DECODE (relrank, '2', name))) NAME2,
MAX (DECODE (rel_id, '1', DECODE (relrank, '3', name))) NAME3
FROM supportContacts
GROUP BY rel_id

heres the SQL fiddle

http://sqlfiddle.com/#!4/480e2/11