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.

Select * from (Select product_Code,Quantity,customer_id from pivot_test) pivot (count(quantity) for product_code in ('A','B','C','D')); 

select * from (select product_code,quantity from pivot_test) pivot(count(*) for product_code in ('A','B','C','D')) 

Does that Mean all columns mentioned in Pivot Clause,If we give asterisk in COUNT() Aggregate Function. For Example

SELECT * FROM(SELECT customer_id,product_code,quantity FROM pivot_test) PIVOT(COUNT(*) FOR product_code IN ('A','B','C'));

Here * Given in Aggregate Function So All Columns from Inner Query
Comes in PIVOT Clause.

Purpose of these questions is to understand the syntax and the relation or dependancy of one syntax on another, ie what is allowed syntactically and what is not, and if allowed what will be its semantics and processing order. The purpose is not to solve any problem so questions may seem like why would one do that , so please I am not looking for answers like "....its not allowed but you can get the same effect by using inline view or WITH clause etc.....". Hope you dont mind.

With new operators in the FROM clause like PIVOT, UNPIVOT, MATCH_RECOGNIZE, my current understanding of SQL query processing is confused, every where I read they explain what the new operators do in ISOLATION and not how they behave with other clauses or processing order, for eg. What if I use WHERE with PIVOT will it be processed before or after PIVOT. What if I use scalar query in SELECT list will it execute before or after PIVOT etc. So to understand conceptually I have these questions.

1.
After using PIVOT operator can I use the name of newly created columns/ALIASES by the PIVOT operator, in WHERE clause in the same SELECT.

Select * from t1 pivot (.... for col1 in( 'A' a))
Where a= 2000

2.
You said PIVOT is syntax sugar, so are PIVOT XML and UNPIVOT also syntax sugar?

3.
Is the table or joined tables or inline view which are on left side of PIVOT keyword, are input table/resultset to the PIVOT operator and the result of PIVOT is output rowset? Same as you explained about MATCH_RECOGNIZE in your youtube video of visualizing MATCH_RECOGNIZE? I know MATCH_RECOGNIZE doesnt allow joined tables on left side as input source unless you encapsulate the join in inline view.

4. Can I join result of PIVOT to another table ie
Can I write this
SELECT * from t1 pivot(..) aliast1p inner join t2 on aliast1p.col1 = t2.col1.

or this

SELECT * from t1 inner join t2 on t1.col1=t2.col1 pivot(..) aliast1t2p inner join t3 on aliast1t2p.col1 = t3.col1

What if joining of above queries are defined in WHERE clause instead of standard ansi Syntax? and that WHERE clause also has other non joining predicates, will the WHERE clause process before PIVOT?

5.
If PIVOT can operate on joined tables, why ORACLE doesnt allow MATCH_RECOGNIZE to operate on joined table.

6. Can I do this
SELECT * FROM t1 PIVOT (...) t1p INNER JOIN t2 PIVOT (...) t2p ON t1p.col1 = t2p.col1