Pivot Function - Transform table rows to colums

PIVOT is very powerful option when data is available in table and requirement is to convert rows in column and display data for those column. One group function is mandatory to use this option.

Syntex -  
SELECT * FROM ( 

SELECT ColumnA,ColumnB,ColumnC,ColumnD 

FROM TABLE_NAME 

WHERE conditions)

PIVOT ( groupFunction(ColumnD) FOR ColumnC in (Values of ColumnC))

Example- assume we have employee expense details for each month. Now requirement is to generate a report which will display each employee’s monthly expenses in column.

Create Table & Insert Values>>

create table test_sandeep(name varchar2(10),dept varchar2(10),month number,amount number);

insert into test_sandeep values('Sandeep','ABC',1,300)

insert into test_sandeep values('Sandeep','ABC',1,120)

insert into test_sandeep values('Sandeep','ABC',2,400)

insert into test_sandeep values('Sandeep','ABC',3,200)

insert into test_sandeep values('Kumar','DEF',2,600)

insert into test_sandeep values('Kumar','DEF',3,700)

insert into test_sandeep values('Kumar','DEF',3,450)

To obtain monthly basis expense amount, we used to write below group by code -

SELECT name,

         dept,

         month,

         SUM (amount)

    FROM test_sandeep

GROUP BY name, dept, month

Using Pivot, we can obtain same data in below manner

SELECT *

  FROM (SELECT name,

               dept,

               month,

               amount

          FROM test_sandeep) PIVOT (SUM (amount) FOR month IN (1 "Jan", 2 "Feb", 3 "Mar"))

Table walkthrough - HRMS

This post to make us familiar with basic HRMS tables. Since for each employee user creation is a normal process, I am mentioning about user details table too.

--User Details

select * from FND_USER where user_name = :name

-- Employee Associated with User

select * from PER_ALL_PEOPLE_F where person_id in (select employee_id from fnd_user where user_name = :name)

-- Assignment for Employee/User

select * from PER_ALL_ASSIGNMENTS_F where person_id in (select employee_id from fnd_user where user_name = :name)

-- Position for Employee/User

select a.name,a.* from PER_ALL_POSITIONS a where position_id in  (select position_id  from per_all_assignments_f where person_id in (select employee_id from fnd_user where user_name = :name))

-- Position Hirerachy for Employee/User

SELECT  ppse.subordinate_position_id,ppse.parent_position_id,pap.name "Subordinate Position"  FROM

PER_POS_STRUCTURE_ELEMENTS ppse, per_all_positions pap WHERE ppse.subordinate_position_id=pap.position_id

START WITH   ppse.subordinate_position_id in  (select position_id  from per_all_assignments_f where person_id in (select employee_id from fnd_user where user_name = :name))

CONNECT BY PRIOR  ppse.parent_position_id = ppse.subordinate_position_id;         

PS – Post intention is to be familiar with HRMS Tables name, below written script can be used by adding additional filter conditions to filter inactive records.

TRUNC(effective_start_date)   <= TRUNC(SYSDATE)

                        AND      TRUNC(effective_end_date)     > TRUNC(SYSDATE)

File Handling using PL/SQL - UTL_FILE

Intention of this post is to list out different exceptions being raised while using UTL_FILE.

First create DBA Directory

File Handling Program -

DECLARE

   p_file_path     VARCHAR2 (200);

   p_file_handle   UTL_FILE.FILE_TYPE;

   p_data_file     VARCHAR2 (200);

BEGIN

   p_data_file := 'SANDEEP_UTLFILE_TEST.txt'; 

   p_file_path := 'SANDEEP_DIR'; 

  
  DBMS_OUTPUT.PUT_LINE('File Path     >>' ||p_file_path );
  DBMS_OUTPUT.PUT_LINE('File Name   >>' ||p_data_file ); 

   p_file_handle := UTL_FILE.FOPEN (p_file_path,p_data_file,'W',32767);// OPEN the FILE

 /********************************************************************************** 

   p_file_path                    >> Location where data file being placed

   p_data_file                    >> Data file name

  ‘W’                               >> Operation Mode  
                                             'W' indicates write mode , 

                                              'R' indicates read mode , 
                                              'A' indicates Append mode

   32767                           >> Length of the string. 32767 is the max length.

 **********************************************************************************/

   UTL_FILE.PUT_LINE (p_file_handle, 'Sandeep is working on UTL File Demo'); 
   // WRITE into FILE
-- UTL_FILE.FFLUSH(p_file_handle); 
-- FFLUSH will write all buffer contents, useful if contents are larger in size.

  UTL_FILE.FCLOSE(p_file_handle);// CLOSE the FILE

EXCEPTION

          WHEN  UTL_FILE.invalid_mode  THEN

            DBMS_OUTPUT.PUT_LINE('20051, Invalid Option in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN UTL_FILE.invalid_path THEN

            DBMS_OUTPUT.PUT_LINE('20052, Invalid Path in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN UTL_FILE.invalid_filehandle THEN

            DBMS_OUTPUT.PUT_LINE('20053, Invalid Filehandle in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN UTL_FILE.invalid_operation THEN

            DBMS_OUTPUT.PUT_LINE('20054, Invalid Operation in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

          WHEN UTL_FILE.read_error THEN

            DBMS_OUTPUT.PUT_LINE('20055, Read Error in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

          WHEN UTL_FILE.write_error THEN

            DBMS_OUTPUT.PUT_LINE('20056, Write Error in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

          WHEN UTL_FILE.internal_error THEN

            DBMS_OUTPUT.PUT_LINE('20057, Internal Error in UTL File le >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN OTHERS THEN

            DBMS_OUTPUT.PUT_LINE('Unexpected Error occured during report generation >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

END;

OUTPUT – File Generated