Pivoting a table in Oracle 10g

Question

I have a query regarding pivoting a table with the following structure and data

Structure

Column Name Data Type
EMP_NAME VARCHAR2(30)

DT NUMBER(2,0)

PRESENT VARCHAR2(1)

EMP_NAME DT PRESENT

V   1   Y
V   2   Y
V   3   Y
V   4   Y
R   1   N
R   2   Y
R   3   Y
R   4   N
K   1   Y
K   2   Y
K   3   Y
K   4   N

I would like to pivot this table to display the name on the left , date on the top and the present in the centre (more like an attendance sheet). I got a code off the internet but that dint really help me. Please help..

Expected Output::

Name 1 2 3 4 5 6 7 8 9 10 11 12.......

R y y n y ......

V n y y n ......

K y y y y ....

The number of names can grow and the data range for dt column will be from 1-31.

Thanks in advance

Vivek

Answer 1

Here is the solution, but you need to know the number of values beforehand. The assumption here is that 'Y' > 'N'.

Also note, that if in fact table some value is missing, like there is no record for some date then MAX function will return null. You should then surround it with NVL clause.

  select EMP_NAME
        ,max(a1) as a1
        ,max(a2) as a2
        ,max(a3) as a3
        ...
    from (select EMP_NAME
                ,decode(DT, '1', Present) as a1
                ,decode(DT, '2', Present) as a2
                ,decode(DT, '3', Present) as a3
                ...
            from test)
group by EMP_NAME
order by EMP_NAME;