SQL (Horizontal and vertical table) row and column conversion, Pivot and Unpivot of the difference and use of examples

The pivot clause is a new feature of Oracle database 11g that enables you to rotate rows into columns in the query output and use aggregate functions on the data. The UNPIVOT clause is also added, and he can rotate columns into rows in the query output;

Introduced

The following table data is now available:

(not shown completely ...) ）

Now we want to observe (1, 2, 3) the sales of each type of ID in the previous April;

You might write SQL like this:

select  prd_type_id, month, sum(amount) from all_sales where month <= 4 and prd_type_id in(1, 2, 3) group by month,prd_type_id order by prd_type_id;

But the results may not be clear, consider if the month is more clearly aligned, if the general method we can write:

Select prd_type_id,SUM (Decode (Month1, amount,0))As JAN,SUM (Decode (Month2, amount,0))As FEB,SUM (Decode (Month3, amount,0))As MAR,SUM (Decode (Month4, Amount,0))As APRFrom All_saleswhere prd_type_idIn1,2,3)Groupby prd_type_id;--not familiar with the Decode function can be used for a methodSelect prd_type_id,SumCaseWhenmonth =1Then amountElse0EndAs JAN,SumCaseWhenmonth =2Then amountElse0EndAs FEB,SumCaseWhenmonth =3 then amount Else 0 end) as-MAR, sum (case is  month = 4 then amount 
                                                                         
                                                                          else 
                                                                          0 end) as APR fromall_saleswhere prd_type_id in (1, 2, 3)Group by prd_type_id;           
                                                                              

Does this show better? But now Oracle 11g provides us with a better way to pivot, and he can turn rows into columns:

SELECT *From (--①Selectmonth, prd_type_id, amount from all_sales where year = 2003  and prd_type_id in (1, 2, 3)) Pivot (--②sum (amount) for month in (1 as JAN, Span class= "Hljs-number" >2 as FEB, 3 as MAR, 4 as APR)) order by prd_type_id;         

Implementation results are the same;
Next, I'll talk about the meaning of the code (just a personal understanding):

For ① My understanding is that this part limits the scope of the data, how is this range determined, first you need to know what you want to show. In this article, you need to display prd_type_id, month, amount three kinds of data, so you can determine;

For ② My understanding is that this section prescribes how to make rows into columns, and for the meaning of sum (amount), let's put it first, say this month, and he prescribes what becomes a column from a row, then in the back part, it specifies which month needs to become a column, and as specifies the alias , can be omitted. So how does the sum (amount) understand, first he must be the aggregation operation, he is to show in each month column to display the data, for January, he has more than one of the following data, which is why the use of aggregate functions, he is exactly what amount sum? We know that in this case, some of the columns have not been transferred, then these columns together with the previous month to specify which amount sum, such as January and id=1 corresponding to the lattice, is all the month is January, and the type ID 1 of the data;

Convert multiple columns

We all know which rows are to be converted to columns, and in that specifies which specific rows to convert, and with these we cannot understand the following SQL:

SELECT *From (Selectmonth, prd_type_id, amountFrom All_saleswhereYear = 2003 and prd_type_id in (1,2,3)) pivot ( sum (amount) for (month, Prd_type_ ID) in ((1, 1) as Jan_prd_type_1, (2, 2) as feb_prd_type_2, (3, 3) as ma R_prd_type_3, (4, 2) as apr_prd_type_2);          

Using multiple aggregate functions in a transformation

SELECT *From (Selectmonth, prd_type_id, amount from all_sales where year = 2003  and prd_type_id in (1, 2, 3)) Pivot (sum (amount) as Sum_amount, avg (amount) as avg_amount for (month) in (1 as JAN, 2 as FEB)) order by prd_type_id;         

It is worth noting that the system will automatically combine two aliases into one;

UNPIVOT clause

The purpose of the UNPIVOT clause is to convert columns to rows.

The existing tabular data is as follows:

select * from pivot_sales_data unpivot(    amount for month in (jan, feb, mar, apr))order by prd_type_id;

SQL (Horizontal and vertical table) row and column conversion, Pivot and Unpivot of the difference and use of examples