Row transposition

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s