Posted by Here’s a couple of simple examples to get you started with transposing rows to columns. The problem typically presents as:
How do you convert a listing in rows (eg, where people placed in a race), into some output that is across the page ?
For example, given a running race, how do you generate output which looks like this:
| RACE_ID | PLACING1 | PLACING2 | PLACING3 | PLACING4 |
So here’s our source table
SQL> create table T ( race_id int, placing int, name varchar2(10));
Table created.
SQL> insert into T values (1,1,'Mike');
1 row created.
SQL> insert into T values (1,2,'Bob');
1 row created.
SQL> insert into T values (1,3,'John');
1 row created.
SQL> insert into T values (2,1,'Sue');
1 row created.
SQL> insert into T values (2,2,'Mary');
1 row created.
SQL> insert into T values (2,3,'Jill');
1 row created.
SQL> insert into T values (2,4,'Jane');
1 row created.
SQL> select * from T order by 1,2;
RACE_ID PLACING NAME
---------- ---------- ----------
1 1 Mike
1 2 Bob
1 3 John
2 1 Sue
2 2 Mary
2 3 Jill
2 4 Jane
A historical technique that could be used on almost any Oracle version is using DECODE
SQL> select race_id,
2 max(decode(placing,1,name)) as placing1,
3 max(decode(placing,2,name)) as placing2,
4 max(decode(placing,3,name)) as placing3,
5 max(decode(placing,4,name)) as placing4
6 from t
7 group by race_id
8 order by 1;
RACE_ID PLACING1 PLACING2 PLACING3 PLACING4
---------- ---------- ---------- ---------- ----------
1 Mike Bob John
2 Sue Mary Jill Jane
or from 11g onwards you can use the PIVOT function.
SQL> select race_id, p1_place, p2_place, p3_place, p4_place
2 from t
3 pivot ( max(name) as place for ( placing ) in ( 1 as p1, 2 as p2, 3 as p3, 4 as p4 ))
4 order by race_id;
RACE_ID P1_PLACE P2_PLACE P3_PLACE P4_PLACE
---------- ---------- ---------- ---------- ----------
1 Mike Bob John
2 Sue Mary Jill Jane
Notice how the column names are ‘dynmaically’ formed from the terms you use in your PIVOT clause.
In both cases, the potential values for PLACING are know in advance.
Learning is not a spectator sport 