Oracle Pivot Query using max decode, Row to Column Conversion

Requirement  1:

Consider scenario where we have table with following data. Here an id can belong to number of groups(grp)

create table test1(id number,grp varchar2(1));

id     grp
—————-
1      A
1      B
2      B
2      C
3      C

Required Output:
Distinct groups are displayed as column and a symbol x indicates that corresponding id is mapped to that group.

id  A   B   C
——————-
1   x   x
2        x     x
3   x

There are fixed number of groups {A B C}, i.e cardinality of column grp is known and also all distinct values.

Solution

We will use the max(decode(..)) technique. This technique is used to convert rows to column and  column to rows.

First we perform decode operation to convert the rows value in to column.
We need to know number of distinct values and also their name in this case. For each distinct group, we write a decode as follows

<pre>SELECT id,
       Decode(grp, 'A', 'x',
                   NULL) a,
       Decode(grp, 'B', 'x',
                   NULL) b,
       Decode(grp, 'C', 'x',
                   NULL) c
FROM   test1
ORDER  BY id

Result:

id  A   B   C
—————-
1  x
1        x
2        x
2               x
3        x

Output will have same number of rows with x  indicating mapping with corresponding id value.

Now we need to group rows by id, since we have distinct values for {id,group} only one x will be appearing for particular combination of id and grp in above result.

SELECT id,
       MAX(Decode(grp, 'A', 'x',
                       NULL)) a,
       MAX(Decode(grp, 'B', 'x',
                       NULL)) b,
       MAX(Decode(grp, 'C', 'x',
                       NULL)) c
FROM   test1
GROUP  BY id
ORDER  BY id

Result:

id  A   B   C
—————
1   x   x
2        x    x
3        x

Requirement 2 : Consider same table and data but now required result is

Required Output:

id   grp1  grp2    grp3
————————–
1    A       B
2    B       C
3    C

that is we know distinct number of groups(we don’t care about values or don’t know values). So, all the groups to which id is mapped we need to show them in columns

Solution:

We will use the same max(decode(..)) technique but with slight modification.First for each id we will give a unique value to each grp value using analytic function row_number()

SELECT id,
       grp,
       Row_number() over (PARTITION BY id ORDER BY grp) rn
FROM   test1

Result

ID GRP RN
———————-
1    A    1
1    B   2
2    B   1
2    C   2
3    C   1

Now we use decode on rn field

SELECT id,
       Decode(rn, 1, grp,
                  NULL) grp1,
       Decode(rn, 2, grp,
                  NULL) grp2,
       Decode(rn, 3, grp,
                  NULL) grp3
FROM   (SELECT id,
               grp,
               Row_number() over (PARTITION BY id ORDER BY grp) rn
        FROM   test1)
ORDER  BY id

ID GRP1 GRP2 GRP3
——————–
1     A        –          -
1     –         B         -
2     B         –          -
2     –          C        -
3     C         –          -

SELECT id,
       MAX(Decode(rn, 1, grp,
                      NULL)) grp1,
       MAX(Decode(rn, 2, grp,
                      NULL)) grp2,
       MAX(Decode(rn, 3, grp,
                      NULL)) grp3
FROM   (SELECT id,
               grp,
               Row_number() over (PARTITION BY id ORDER BY grp) rn
        FROM   test1)
GROUP  BY id
ORDER  BY id

Output:

ID GRP1 GRP2 GRP3
1      A      B       -
2       B      C       -
3       C       –       -

About these ads
1 comment
  1. Anonymous said:

    very helpful.. thanks so much..

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: