Pivot and Unpivot

Pivot and Unpivot Use a simple SQL spreadsheet-type crosstab report to display information from any relational table and store all the data in a crosstab table in a relational table. Pivot As you know, relational tables are tabular, that is, they appear in the form of column-value pairs. Suppose a table name is CUSTOMERS. Sql> desc Customers Name Null? Type ---------------------------------------------------------------------------- cust_id number ( cust_name VARCHAR2 () state_code VARCHAR2 (2) times_purchased number (3) Select the table: Select cust_id, State_code, times_purchased from Customers order by cust_id; The output results are as follows: cust_id state_code times_purchased -------------------------------- 1 CT 1 2 NY 3 NJ 2 4 NY 4 ... and so on ... Notice how the data is displayed as a row value: For each customer, the record shows the state of the customer and the number of times the customer has shopped in the store. When the customer buys more items from the store, the column times_purchased is updated. Now, suppose you want to count a report to see how often the states are buying, that is, how many customers in each state are shopping only once, twice, three times, and so on. If you use regular SQL, you can execute the following statement: Select State_code, Times_purchased, COUNT (1) CNT from customers GROUP by State_code, times_purchased; The output is as follows: ST times_purchased CNT --------------------------- ct 0 Ct 1 165 CT 2 179 CT 3 173 CT 4 173 CT 5 152 ... and so On ..... This is the information you want, but it doesn't look very convenient. You might be able to better display the data by using a crosstab report, so that you can arrange the data vertically and arrange the states horizontally, just like spreadsheets: times_purchased CT NY NJ ... and so on ... 1 0 1 0 ... 2 119 ... 3 1 ... ... and so on ... Before Oracle database 11g is launched, you need to do this through the DECODE function for each value and write each of the different values as a separate column. However, the method is not intuitive at all. Thankfully, you can now use a great new feature PIVOT to display any query in a crosstab format with a new operator called PIVOT. The following is how the query is written: SELECT * FROM ( select times_purchased, state_code from customers T ) pivot ( count ( State_code) for State_code in (' NY ', ' CT ', ' NJ ', ' FL ', ' MO ') "ORDER by times_purchased / The output is as follows: . times_purchased ' NY ' CT ' NJ ' FL ' MO ' --------------------------------------------- -------------------- 0 16601 0 0 0 1 33048 165 0 0 0 2 33151 179 0 0 0 3 32978 173 0 0 0 4 33109 173 0 1 0 ... and so on ... This shows the power of the pivot operator. State_codes is displayed as a header row instead of a column. The following are illustrations of the traditional tabular format: Figure 1 traditional tabular display in a crosstab report, you want to swap the position of the Times purchased column to the header row, as shown in Figure 2. The column becomes a row, just as if the column rotates 90 degrees counterclockwise and becomes the header row. The symbolic rotation needs to have a fulcrum (pivot point), in this case the fulcrum is a count (state_code) expression. Figure 2 performs the display of the Pivot operation this expression needs to take the following query syntax: ... Pivot ( count (state_code) for State_code in (' NY ', ' CT ', ' NJ ', ' FL ', ' MO ') ... The second line "for State_code ..." Restricts query objects to only those values. The line is required, so unfortunately, you need to know the possible values in advance. The restrictions in XML-formatted queries will be relaxed, as described later in this article. Note the header row in the output: . times_purchased ' NY ' CT ' NJ ' FL ' MO ' --------------------------------------------- ---------- ---------- The column headings are data from the table itself: the state code. The abbreviation may be quite clear without further explanation, but suppose you want to display state names rather than abbreviations ("Connecticut" rather than "CT"). If so, you need to make some adjustments in the FOR clause of the query, as follows: SELECT * FROM ( select Times_purchased as "Puchase Frequency" and State_code from customers T ) pivot ( count (state_code) for State_code in (' NY ' as "New York", ' CT ' "Connecticut ', ' NJ '" New Jersey ", ' FL '" Florida ", ' MO ' as "Missouri") Order by 1 / puchase Frequency new York Connecticut new Jersey florida< C13/>missouri -------------------------------------------------------------------- 0 16601 0 0 0 1 33048 165 0 0 0 2 33151 179 0 0 0 3 32978 173 0 0 0 4 33109 173 0 1 0.. and so on ... The FOR clause can provide an alias for the values in which these values will become column headings.Unpivot Just like matter has antimatter, there should be "Unpivot" in pivot, right. Well, no kidding, but Pivot's reverse operation is really needed. Suppose you have a spreadsheet that displays a crosstab report, as follows: Purchase Frequency New York Connecticut New Jersey Florida Missouri 0 12 11 1 0 0 1 900 14 22 98 78 2 866 78 13 3 9 ... . Now, you want to load this data into a relational table named CUSTOMERS: Sql> desc Customers Name Null? Type ---------------------------------------------------------------------------- cust_id number ( cust_name VARCHAR2 () state_code VARCHAR2 (2) times_purchased number (3) Spreadsheet data must be normalized to relational format and then stored. Of course, you can use DECODE to write a complex sql*:loader or SQL script to load data into a CUSTOMERS table. Alternatively, you can use the pivot reverse operation Unpivot to break the column into rows, which can be implemented in Oracle database 11g. It may be simpler to demonstrate this through an example. Let's start by using the pivot action to create a crosstab: 1 CREATE TABLE Cust_matrix 2 as 3 select * FROM ( 4 Select Times_purchased as "puchase Frequency ", State_code 5 from customers T 6 ) 7 pivot 8 ( 9 Count (State_code) State_code in (' NY ' as "New York", "CT" "Conn", ' NJ ' "New Jersey", ' FL ' "Florida", ' MO ' as "Missouri") One ) 12* ORDER by 1 You can view how data is stored in a table: Sql> SELECT * from Cust_matrix 2 / puchase Frequency new York Conn new Jersey Florida Missouri ------------------------------------------------------------------- 1 33048 165 0 0 0 2 33151 179 0 0 0 3 32978 173 0 0 0 4 33109 173 0 1 0 ... This is how data is stored in a spreadsheet: Each state is a column in the table ("New York", "Conn", and so on). sql> desc cust_matrix Name Null? Type ---------------------------------------------------------------------------- puchase Frequency Number (3) New York number Conn number new Jersey number Florida number Missouri Number You need to upset the table so that the line shows only the state code and the number of shoppers in the state. This can be accomplished by UNPIVOT operations, as follows: SELECT * from Cust_matrix unpivot ( state_counts to State_code in ("New York", "Conn", "new Jersey "," Florida "," Missouri ") ORDER BY" Puchase Frequency ", State_code / The output is as follows: Puchase Frequency state_code state_counts --------------------------------------- 1 Conn 165 1 Florida 0 1 Missouri 0 1 New Jersey 0 1 New York 33048 2 Conn 179 2 Florida 0 2 Missouri 0 ... and so on ... Notice how each column name changes to a value in the State_code column. How Oracle knows that State_code is a column name. It is known by the clauses in the query, as follows: For State_code in ("New York", "Conn", "New Jersey", "Florida", "Missouri") Here, you specify the "New York", "Conn" equivalent is the value of the State_code column that you want to perform a unpivot operation on. Let's take a look at some of the raw data: Puchase Frequency New York Conn new Jersey Florida Missouri ----------------------------- -------------------------------------- 1 33048 165 0 0 0 How do you display a value of 33048 when column "New York" suddenly becomes a value in a row? The value should be displayed under which column. This is answered by the clause above in the FOR clause in the UNPIVOT operator in the above query. You specified state_counts, which is the name of the new column created in the generated output. Unpivot can be a pivot reverse operation, but do not assume that the former can reverse any operation of the latter. For example, in the example above, you created a new table Cust_matrix using the pivot action for the CUSTOMERS table. You then used Unpivot for the Cust_matrix table, but this did not retrieve the details of the original table CUSTOMERS. Instead, crosstab reports are displayed differently to allow you to load data into a relational table in different ways. Therefore Unpivot is not intended to cancel the operation of the pivot. You should consider carefully before you use pivot to create a table and then delete the original table. Some of the interesting uses of Unpivot are beyond the usual powerful range of data operations (examples above). Amis Technologies Oracle ACE Director Lucas Jellema describes how to generate several rows of specific data for testing. Here, I will modify his original code slightly to show the vowels in the English alphabet: Select Value from ( Select ' A ' v1, ' E ' v2, ' i ' V3, ' o ' v4, ' U ' v5 from dual ) Unpivot ( value for value_type in (V1,V2,V3,V4,V5) ) ) / The output is as follows: V - a e i o u The model can be extended to include any type of row builder. Thanks to Lucas for providing us with this ingenious technique.XML Type In the preceding example, notice how you specify a valid state_codes: For State_code in (' NY ', ' CT ', ' NJ ', ' FL ', ' MO ') This requirement assumes that you know the values that are displayed in the State_code column. If you don't know what the values are, how do you build the query? Another clause in the pivot action XML can be used to resolve this issue. This clause allows you to create an output in XML format that performs a pivot operation, in which you can specify a special clause any rather than a literal value. Examples are as follows: SELECT * FROM ( select Times_purchased as "Purchase Frequency", State_code from customers T ) pivot x ML ( count (state_code) for State_code in (NO) Order by 1 / The output reverts to CLOB to ensure that longsize is set to a large value before the query runs. Sql> Set Long 99999 The query has two distinct differences (shown in bold) compared to the original pivot operation. First, you specify a clause pivot XML instead of just pivot. This clause generates output in XML format. Second, the FOR clause displays the State_code value for the State_code in (any) instead of the long list. The XML notation allows you to use the Any keyword, and you do not have to enter a State_code value. The output is as follows: Purchase Frequency state_code_xml-------------------------------------------------------------------- 1 <pivotset><item><column name = "State_code" >ct</co lumn><column name = "COUNT (State_code)" >165</colum n></item><item><column name = "State_code" >N Y</col umn><column name = "COUNT (state_code)" >33048</colu mn></ Item></pivotset> 2 <pivotset><item><column name = "State_code" >ct</co Lumn><column name = "COUNT (state_code)" >179</colum n></item><ite M><column name = "State_code" >ny</col umn><column name = "COUNT (state_code)" >33151 </colu mn></item></pivotset> ... and on ... As you can see, column state_code_xml is XmlType, where the root element is <PivotSet>. Each value is represented in the form of a name-value element pair. You can use the output from any XML parser to generate more useful output. In addition to the ANY clause, you can write a subquery. Suppose you have a list of priority states and want to select only those states ' rows. You place the priority state in a new table named Preferred_states: Sql> CREATE TABLE Preferred_states 2 ( 3 state_code varchar2 (2) 4 ) 5 / Table created. sql> INSERT into preferred_states values (' FL ') 2>/ 1 row created. Sql> commit; Commit complete. Now the pivot operation looks like this: SELECT * FROM ( select Times_purchased as "Puchase Frequency", State_code from customers T ) pivot XM L ( count (state_code) for state_code in (select State_code from Preferred_states) ) 1 / Subqueries in the FOR clause can be anything you need. For example, if you want to select all records without being limited to any of the priority states, you can use the following as a FOR clause: For State_code in (select distinct State_code from Customers) The subquery must return a different value, or the query will fail. This is why we specify the DISTINCT clause above.Conclusions The Pivot adds a very important and useful feature to the SQL language. You can use the PIVOT function to create a crosstab report for any relational table without having to write inexplicable, intuitive code that contains a large number of decode functions. Similarly, you can use the Unpivot action to convert any crosstab report to store it as a regular relational table. Pivot can generate regular text or XML-formatted output. In the case of XML-formatted output, you do not have to specify the range of search for the pivot operation.