SQL: The PIVOT clause

The PIVOT clause allow you to display data of different categories in one row. It is a new feature added in 11g.

Let's say you have the following Inventory table.

StoreName             Product           Quantity
----------------         --------------      ----------
store 1                    Apple               100
store 1                    Banana             350
store 1                    Pineapple         300
store 2                    Apple               150
store 2                    Banana              300
store 2                    Pineapple          250
store 3                    Apple                200
store 3                    Banana              180
store 3                    Pineapple           260

SQL> select * from Inventory where not StoreName = 'store 3' order by StoreName;

Output:

StoreName             Product           Quantity
----------------         --------------      ----------
store 1                    Apple               100
store 1                    Banana             350
store 1                    Pineapple         300
store 2                    Apple               150
store 2                    Banana              300
store 2                    Pineapple          250

If you want to view all the products of each store in one row, the PIVOT clause will help.

SQL> select * from
           (
                  select StoreName, Product, Quantity from Inventory
           )
            PIVOT
           (
                   sum(Quantity) as Qty for Product in ('Apple' as Apple, 'Banana' as Banana, 'Pineapple' as Pineapple)
            )
            where not StoreName = 'store 3' order by StoreName;

Output:

StoreName         Apple_Qty         Banana_Qty        Pineapple_Qty
--------------        -------------          --------------          ----------------
store 1                100                     350                       300
store 2                150                     300                       250

SQL> select * from
           (
                 select StoreName, Quantity from Inventory
            )
            PIVOT
            (
                     sum(Quantity) for StoreName in ('store 1', 'store 2', 'store 3')
            );

Output:

store 1           store 2           store 3
---------         ---------           ---------
750               700                 640

SQL> select StoreName, sum(Quantity) as "Total Quantity" from Inventory group by StoreName;

Output:

StoreName           Total Quantity
-------------            ------------------
store 1                   750
store 2                    700
store 3                   640

-----------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

SQL: Check if a column exists in a table / a table has a column

You can query the user_tab_cols, all_tab_cols, or dba_tab_cols to see if a table has a column.

SQL> select count(*) from user_tab_cols where column_name='<column name>' and table_name='<table name>';

or

SQL> select count(*) from all_tab_cols where column_name='<column name>' and table_name='<table name>' and owner='<owner>';

or

SQL> select count(*) from dba_tab_cols where column_name='<column name>' and table_name='<table name>' and owner='<owner>';

-----------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.