BIK Information Services, Inc.

Oracle PL/SQL snippets

Pivoting the results of a select statement
Problem at hand: We have several rows selected as a result of a select statement. We are only interested in one value from each row. We know that we can only have no more than 3 or 4 of the rows for each key. We want to "flatten" the result set - display one row per key with the values as different columns instead of rows.

Example

For instance:

with example as ( select 1 key1, 10 seq, 'abc1' code from dual union select 1 key1, 20 seq, 'abc2' code from dual union select 1 key1, 30 seq, 'abc3' code from dual union select 1 key1, 40 seq, 'abc4' code from dual union select 1 key1, 50 seq, 'abc5' code from dual union select 2 key1, 100 seq, 'bc1' code from dual union select 2 key1, 200 seq, 'bc2' code from dual union select 2 key1, 300 seq, 'bc3' code from dual union select 2 key1, 400 seq, 'bc4' code from dual ) select * from example order by key1, seq
produces the following result:

key1 seq code

1 10 abc1

1 20 abc2

1 30 abc3

1 40 abc4

1 50 abc5

2 100 bc1

2 200 bc2

2 300 bc3

2 400 bc4

but what we really want is this:

key1 code1 code2 code3 code4 code5

1 abc1 abc2 abc3 abc4 abc5

2 bc1 bc2 bc3 bc4

Solution. (Oracle 9i+).

Let's consider SQL like this:
with example as ( select 1 key1, 10 seq, 'abc1' code from dual union select 1 key1, 20 seq, 'abc2' code from dual union select 1 key1, 30 seq, 'abc3' code from dual union select 1 key1, 40 seq, 'abc4' code from dual union select 1 key1, 50 seq, 'abc5' code from dual union select 2 key1, 100 seq, 'bc1' code from dual union select 2 key1, 200 seq, 'bc2' code from dual union select 2 key1, 300 seq, 'bc3' code from dual union select 2 key1, 400 seq, 'bc4' code from dual ) select * from ( select key1, seq , row_number() OVER (PARTITION BY key1 order by seq) rn , code code1 , lead(code,1,null) OVER (PARTITION BY key1 order by seq) code2 , lead(code,2,null) OVER (PARTITION BY key1 order by seq) code3 , lead(code,3,null) OVER (PARTITION BY key1 order by seq) code4 , lead(code,4,null) OVER (PARTITION BY key1 order by seq) code5 from example order by key1, seq ) where rn = 1 order by key1
It will produce the desired result:

key1 rn seq code1 code2 code3 code4 code5

1 10 1 abc1 abc2 abc3 abc4 abc5

2 100 1 bc1 bc2 bc3 bc4

We hope you found this snippet useful.
If so drop us a note to Boris Knizhnik borisk@bikinfo.com.
We appreciate any links to this site as they show your appreciation and work as free ads.

Last modified on Wednesday, 02-Feb-2005 23:16:41 EST

Back to BIK Information Services home page