Thank you for the answer. i went through the link given in the answer and I understood about pivot as "Pivot Implicitly Groups the All Columns not Listed in Aggregate Function"
For Example
Select * from (Select product_Code,Quantity,customer_id from pivot_test) pivot (count(quantity) for product_code in ('A','B','C','D'));
Pivot Implicitly Groups the identical rows of Columns product_code and Customer_id, and display the Count of not null rows of quantity column in each identical group of Product_code and Customer_id
For Example Consider below data
Customer_id Product_code Quantity
1 A 10
1 B 10
1 A 20
1 A
So Pivot forms 2 group for above pivot query like below
1,3,4 are as one group and 2 row as another group. Total Row count in 1st Group is 3, but Count Of Quantity is 2 because one row of quantity column in the 1st Group is null so pivot display as 2 for the Count(Quantity);
"Is Scenario i explained about pivot correct?"
If Correct When i replaced Quantity column with * in the Aggregate function of Pivot Query,it should display all the columns in the table because it does not group any columns.
But when i executed below statement It Combining the Identical Values of Quantity Column and giving the Count of rows in that identical combination
select * from (select product_code,quantity from pivot_test) pivot(count(*) for product_code in ('A','B','C','D'))
Below is the Live SQl Link
https://livesql.oracle.com/apex/livesql/s/evmpcymiwxw4u9743o4kav8xi Thanks in Advance.
April 21, 2017 - 3:47 pm UTC
In
Select * from (Select product_Code,Quantity,customer_id from pivot_test) pivot (count(quantity) for product_code in ('A','B','C','D'));
Quantity and product_code are both in the pivot clause. So it only implicitly groups by customer_id.
With:
select * from (select product_code,quantity from pivot_test) pivot(count(*) for product_code in ('A','B','C','D'))
Product_code is in the pivot clause again. So you get an implicit group by quantity.