PIVOT clause in Orcale

PIVOT and UNPIVOT clause in Oracle - this is something I learnt recently.

PIVOT and UNPIVOT is introduced from Oracle 11g version.
This post will explain about PIVOT clause in Oracle.

PIVOT

Assume a scenario as below.

You have a table which stores some parameter information. it has two columns called PARAMETER_NAME and PARAMETER_VALUE those are used to save parameter names and values.
Now you get a requirement, that you need to create a new view  from old table. But, in this new view, there should be a separate column for each parameter. 

To achieve this requirement, you can get help of PIVOT clause.

PIVOT clause is allowed you to transpose the rows in a table to columns by aggregating the data.
Hence, PIVOT clause returns the more columns and fewer rows than the starting data set (table).

Syntax for PIVOT clause.

SELECT 
    col0, col1, col2
FROM 
    table_name
PIVOT 
(
  aggregate_function(col2)
  FOR col1
  IN ( expr1, expr2, ... expr_n)
)



  • Pivot clause - column that you want to aggregate with oracle aggregate function ex: SUM, MAX, MIN, COUNT, AVG

                AGGREGATE_FUNCTION(col2)


  • Pivot for clause - The column you want to Pivot
                FOR col1

  • Pivot in clause - Values of the col1 that need to transpose to columns.
               IN ( expr1, expr2, ... expr_n)

PIVOT clause perform implicit ORDER_BY based on the all other selected columns those are not defined in the PIVOT clause.

Example


Old table.

CREATE TABLE PIVOT_TEST
( id number(10) NOT NULL,
  parameter_name varchar2(50) NOT NULL,
  parameter_value varchar2(50)
);

Data in PIVOT_TEST will look like as below.



You can create a query as below to fetch the data from tables with using PIVOT clause. 

WITH old AS
   (
      SELECT id, parameter_name, parameter_value
        FROM PIVOT_TEST
   )
   SELECT * FROM old
   PIVOT
   (
      MAX(parameter_value)
      FOR parameter_name IN
      (
          'MAX_VALUE' AS max_value,
          'MIN_VALUE' AS min_value,
          'OPERATION_METH' AS operation_meth,
          'CONNECTOR_TYPE' AS connector_type
      )
    )

Here, I have used MAX as the aggregate function. MAX or MIN aggregate functions can be used, when you want to show original values of the columns as it is. This MIN and MAX aggregate functions are mostly used with non numeric values.

Then the result will be as below.









Comments

Post a Comment

Popular posts from this blog

How to add standard and custom Metadata to PDF using iTextSharp

In a previous blog, I explained How to add custom metadata using Syncfusion library. But this package gives so many difficulties when adding metadata to pdf. Such problems are,    1. When once add custom meta data to a pdf, it does not able to change or remove those custom meta data from the pdf.    2. It does not compatible with meta data search engines. It does not show custom meta data properly when those are searched through the search engines. Due to these reasons, Syncfusion is not good library for custom meta data. But, iTextSharp provides flexible framework to deal with custom meta data as well as standard meta data for PDF. Below I have provided the source code to show usage of iTextSharp library. You can download iTextShrp.dll from here                              PdfReader pdf = new PdfReader(input_path); ...
Read more

How to set and get Metadata to image using BitmapMetadata class in C#

When images are considered, those are storing their Meta data using different types of Meta containers such as EXIF, Adobe XMP, IPTC, Photoshop Image resource and Text descriptions. This every container has their specific unique rules to encode (store) and decode (extract) Meta data. As well as these each Meta container is storing specific Meta information. Exif Meta containers are mostly storing location information and GPS (Global Positioning System) records. As well as, Adobe XMP is responsible to store Dublin core Meta data which provides standard Meta tags that can be used to describe digital objects basically and IPTC is storing Meta information about application records. Meta data extractor is mainly considering these Exif, IPTC and adobe XMP Meta containers when it is extracting Meta data from images. As well as, these different Meta data containers are considered; they are structuring Meta tags in different dir...
Read more

How to generate direct line token to start a new conversation between your own client application and bot framework in Java

You can enable the communication between your bot framework and your client application using Direct Line API. When you add your client application as a site to communicate with your bot framework through azure portal, bot framework will generate secret keys. Your client application can use these generated secret keys to authenticate direct line requests issued to your bot framework. Let me explain how we can generate token to start a  conversations between your own client and bot framework. When we send a request to direct line for generating token,  if the request is successful it will send the conversation id and token that is valid to that conversation id. If client application already has a earlier used conversation Id, you can pass that id as an input to this method for generating token. If there is no conversation id, you can pass null value.  Return type of this method is a Map to retrieve generated token and conversation Id which is valid for that to...
Read more