Oracle / PLSQL: PIVOT

 

Oracle / PLSQL: PIVOT Clause

This Oracle tutorial explains how to use the Oracle PIVOT clause with syntax and examples.

Description

The Oracle PIVOT clause allows you to write a cross-tabulation query starting in Oracle 11g. This means that you can aggregate your results and rotate rows into columns.

Syntax

The syntax for the PIVOT clause in Oracle/PLSQL is:

SELECT * FROM
(
  SELECT column1, column2
  FROM tables
  WHERE conditions
)
PIVOT 
(
  aggregate_function(column2)
  FOR column2
  IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

aggregate_function

It can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

Example

CREATE TABLE orders
( order_id integer NOT NULL,
  customer_ref varchar2(50) NOT NULL,
  order_date date,
  product_id integer,
  quantity integer,
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

To show you the data for this example, we will select the records from the orders table with the following SELECT statement:

SELECT order_id, customer_ref, product_id
FROM orders
ORDER BY order_id;

These are the records in the orders table. We will be using these records to demonstrate how the PIVOT clause works:

order_id	customer_ref	product_id
50001	SMITH	10
50002	SMITH	20
50003	ANDERSON	30
50004	ANDERSON	40
50005	JONES	10
50006	JONES	20
50007	SMITH	20
50008	SMITH	10
50009	SMITH	20

Now, let's create our cross-tabulation query using the following PIVOT clause:

SELECT * FROM
(
  SELECT customer_ref, product_id
  FROM orders
)
PIVOT
(
  COUNT(product_id)
  FOR product_id IN (10, 20, 30)
)
ORDER BY customer_ref;

In this example, the PIVOT clause would return the following results:

customer_ref	10	20	30
ANDERSON	0	0	1
JONES	1	1	0
SMITH	2	3	0

Now, let's break apart the PIVOT clause and explain how it worked.

Specify Fields to Include

First, we want to specify what fields to include in our cross tabulation. In this example, we want to include the customer_ref and product_id fields. This is done by the following portion of the statement:

(
  SELECT customer_ref, product_id
  FROM orders
)

Skip Command in Form 6i ==

 

Skip Command in a loop .. 

if value >0 then

goto  skip_cmd;

----------

-

-

-

-

-

-

<<skip_cmd>>

Note : In middile goto to      <<skip_cmd>> commansed by pass.

RDBMS and SQL

INTRODUCTION

This SQL Guide is written as a basic introduction to Structured Query Language (SQL).

RDBMS and SQL

Structured Query Language (SQL) is a standard language for communication with a relational database management system (RDBMS). SQL provides commands which are used to create, modify, and access the database. Databases contain one or more tables of data with relationships between multiple tables defined in the database. Each table is an object.

SQL Commands

• Select - Used to retrieve selected data. Example:

select "columnname1", "columnname2" from "tablebasename" [where "condition"];

A "*" may be used to select all columns. The where clause is optional and only one column name must be specified.

• Insert
• Update
• Delete
• Create
• Drop

Insert Command

This valment is used to insert a row of data in a table. All inserted values are enclosed       using single quote strings. The syntax of this command is:

insert into tablename

(column1name,column2name...columnxname)

values (value1,value2...valuex); 

Example

insert into Abcd

(name, val, indi, zipcode)

values ('Type', 'Apple', '89', '46501');

Update Command

This command is used to make changes to records in tables. Syntax:

update tablename

set columnname = newvalue [,columnxname = newvaluex...]

where columnname OPERATOR value [and|or columnnamex OPERATOR valuex];

The OPERATOR is one of the conditions listed on the Select Command page.

OPERATORs include:

• =   Equal
• <   Less than
• >   Greater than
• <= Less than or equal
• >= Greater than or equal
• <> Not equal
• LIKE - Allows the wildcard operator, %, to be used to select items that are a partial match. An example is:

select city, val from towntable where val like 'bcd%'

This allows selection of all towns in vals that begin with the word "north" allowing vals like North Dakota and North Carolina to be selected.

Example

update Abcd set indi = indi+1 where name = 'Type' and val = 'Apple';

Delete Command

This command is used to delete rows or records in a table. The syntax of this command is:

delete from "tablename"

where columnname OPERATOR value [and|or 

columnnamex OPERATOR valuex]; 

Note : without the where clause, all records in the table will be deleted.

Example

delete from Abcd where name = 'Type' and val = 'Apple';

Create Command

This command is used to create a table. The syntax of this command is:

create table tablename

(column1name datatype [constraint],

column2name datatype [constraint], 

column3name datatype [constraint]);

Keywords specifying Items that may be created

• table

Data Types

• char(size) - Fixed length string of characters of the set size. The size of the string is limited to 255 characters.
• date
• number(maxsize) - Number with a maximum number of digits specified by "maxsize".
• number(maxdigits,maxright) - A decimal number with a manimum number of "maxdigits" with "a maximum number of digits to the right of the decimal, "maxright".
• varchar(maxsize) - A character string with variable lingth limited to "maxsize".

Constraints

Constraints are rules for the column.. Possible values include:

• not null - The column values must have a value and cannot be null.
• primary key - Each record is uniquely identified by this column.
• unique - No two values may be the same in the column

Example

create table Abcd (name varchar(20),val varchar(20),indi number(8),

zipcode number(5) unique);

 Drop Command

     Used to remove an entire table from the database. Syntax:

  drop table <tablename>;

Example

drop table Abcd;