How to transform vertical data into horizontal data with SQL?
Solution 1
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
Solution 2
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 :-)
Solution 3
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
Dan
Updated on July 09, 2022Comments
-
Dan almost 2 years
I have a table "Item" with a number of related items, like so:
ID Rel_ID Name RelRank --- ------ ---- ------- 1 1 foo 1 2 1 bar 2 3 1 zam 3 4 2 foo2 1
I'm trying to get a query so items with the same Rel_ID would appear in the same row, like so:
Rel_ID Name1 Name2 Name3 ------ ----- ----- ----- 1 foo bar zam 2 foo2
I've tried selecting the table multiple times:
SELECT k.Rel_ID, k.name 'Name1', k2.name 'Name2' FROM item k, item k2 WHERE k.Rel_ID = k2.Rel_ID
But this fails. Surely there's a transformation or query that could drastically simplify the process, and I'm just missing it because I haven't used SQL in this way before. What am I missing?
[Edit: added RelRank column, which does appear in my data]