Oracle SQL Cross Tab Query

Question

I have a table which has the following structure and sample data:

ITEM   LOC   STOCK
0001   KS5    10
0001   KS6    30
0002   KS5    10
0002   KS6    20

I need to query cross tab so that I get

ITEM  KS5  KS6
0001  10   30
0002  10   20

The LOC (KS5 and KS6) can vary and new locations can be added.

How can I get the desired result?

Answer 1

Please try this query .

SELECT *
FROM   (SELECT ITEM ,LOC ,STOCK
        FROM   TABLE_NAME)
PIVOT  (SUM(STOCK) FOR (LOC) IN ('KS5' , 'KS6'))
ORDER BY ITEM;

Regards.