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)

[sourcecode language="sql"]
create table test1(id number,grp varchar2(1));
[/sourcecode]

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

[sourcecode language="sql"]
<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
[/sourcecode]

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.

[sourcecode language="sql"]
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
[/sourcecode]

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

[sourcecode language="sql"]
SELECT id,
grp,
Row_number() over (PARTITION BY id ORDER BY grp) rn
FROM test1
[/sourcecode]

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

[sourcecode language="sql"]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

[/sourcecode]

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

[sourcecode language="sql"]
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
[/sourcecode]

Output:

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