How to transform vertical data into horizontal data with SQL?

Question

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]

It's a pivot query you're after, but there doesn't appear to be a consistent means of knowing what name is first, second or third if the id value is not resetting for each rel_id value. — OMG Ponies, Nov 01 '10 at 18:24
Are there a maximum amount of names for rel_id? or is unlimited? — Mauro Gagna, Nov 01 '10 at 18:26
OMG Ponies—there is a RelRank column which should address your comment. Edited to show it — Dan, Nov 01 '10 at 18:35
Mgagna—Rel_ID actually points to another table. In theory there's no limit but in practice it's quite a finite set. — Dan, Nov 01 '10 at 18:35

Falcon · Accepted Answer · 2010-11-01T18:43:28.350

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

Thanks Falcon—this is exactly what I was looking for! I'll leave the question open for a bit to see if any other categories of solutions turn up. — Dan, Nov 01 '10 at 18:39

smartnut007 · Answer 2 · 2016-03-12T16:27:10.613

4

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 :-)

edited Mar 12 '16 at 16:27

answered Nov 01 '10 at 18:34

smartnut007

6,324
6
45
52

score 1 · Answer 3 · edited Oct 14 '14 at 12:58

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

How to transform vertical data into horizontal data with SQL?

3 Answers3

Linked