Oracle row-to-column operations

Sometimes when we show the data in the table, we need to display the rows into columns, as in the following form:

The original table structure is shown below:
---------------------------
Product Name Sales Quarter
---------------------------
Cheese 50 First quarter
Cheese 60 Second quarter
Beer 50 second quarter
Beer 80 Quarter IV
---------------------------

Now it is necessary to convert the original table structure above into the structure shown below:
--------------------------------------------------------------------------
Product name first-quarter sales second-quarter sales third-quarter sales sales in quarter IV
--------------------------------------------------------------------------
Cheese 50 60 0 0
Beer 0 50 0 80
--------------------------------------------------------------------------

First, create the Sales table SALE_HST table structure

-- Create a sales table Create Table sale_hst (    varchar2),-- Product name number    (8 ),-- sales    varchar2(ten)-- quarterly );

Second, insert the basic data

--Insert the underlying data as shown aboveInsert  intoSale_hstValues('Cheese', -,'First quarter');Insert  intoSale_hstValues('Cheese', -,'second quarter');Insert  intoSale_hstValues('Beer', -,'second quarter');Insert  intoSale_hstValues('Beer', the,'fourth quarter');

III. using SQL statement conversions

Scenario 1: Using Case...when...then...else...end ... Statement

--Scenario 1: Using Case...when...then...else...end ... StatementSelectPrdt_name,sum( Case  whenSeason='First quarter'  ThenSale_amtElse 0 End) First-quarter sales,sum( Case  whenSeason='second quarter'  ThenSale_amtElse 0 End) Second-quarter sales,sum( Case  whenSeason='third quarter'  ThenSale_amtElse 0 End) third-quarter sales,sum( Case  whenSeason='fourth quarter'  ThenSale_amtElse 0 End) fourth quarter sales fromSale_hstGroup  byPrdt_name;

The Decode function can be used to deal with the scheme 2:oracle

Description

Oracle can be processed with the Decode function:
The decode function is one of the most powerful functions in Oracle PL/SQL, and currently only Oracle company SQL provides this function, and the SQL implementation of other database vendors does not yet have this capability.

The Decode function functions as follows:
decode (field or Field operation, value 1, value 2, value 3)
The result of this function is that when the value of the field or Field operation is equal to the value 1 o'clock, the function returns the value 2, otherwise the value 3
Of course, a value of 1, a value of 2, a value of 3 can also be an expression, which makes some SQL statements much simpler.

--the Decode function can be used to deal with the scheme 2:oracleSelectPrdt_name,sum(Decode (season,'First quarter', Sale_amt,0)) asfirst quarter sales,sum(Decode (season,'second quarter', Sale_amt,0)) assales in the second quarter,sum(Decode (season,'third quarter', Sale_amt,0)) asthird-quarter sales,sum(Decode (season,'fourth quarter', Sale_amt,0)) asfourth quarter sales fromSale_hstGroup  byPrdt_name;

Sometimes we have the following requirements:

The data form of the original table is shown as follows:

Shopping Table:
----------------------------------
U_ID Goods num
----------------------------------
1 Apples 2
2 Pear 5
1 Watermelon 4
3 Grapes 1
3 Banana 1
1 Oranges 3
----------------------------------

Converted to the form shown below 1 show:

--------------------------------------------
u_id Goods_sum Total_num
--------------------------------------------
1 apples, watermelon, oranges 9
2 Pear 5
3 Grapes, Banana 2
--------------------------------------------

Converted to the form shown below 2 show :
------------------------------------------------------
u_id Goods_sum Total_num
------------------------------------------------------
1 apples (2 kg), watermelon (4 kg), Orange (3 kg) 9
2 pears (5 kg) 5
3 grapes (1 kg), banana (1 kg) 2
------------------------------------------------------

First, create a shopping table shopping table structure

-- Create a shopping table shopping Create Table Shopping (    number),    varchar2(8),      Number (ten)   );

Second, insert the basic data

--Insert the underlying data as shown aboveInsert  intoShoppingValues(1,'Apple',2);Insert  intoShoppingValues(2,'Pear',5);Insert  intoShoppingValues(1,'Watermelon',4);Insert  intoShoppingValues(3,'Grape',1);Insert  intoShoppingValues(3,'Banana',1);Insert  intoShoppingValues(1,'Orange',3);

III. using SQL statement conversions

Form 1:

-- Statement of Form 1 Select u_id, wmsys.wm_concat (goods) goods_sum,sum(num) total_num   from Shopping   Group by u_id;

Form 2:

-- Statement of Form 2 Select || ' ('| | || ' Jin) ' ) Goods_sum,sum(num) total_num from   shopping  group by u_id;

Description

Use of wm_concat (column) functions in Oracle:
Wmsys User's wm_concate function
In an Oracle database, with WM_CONCAT (column) functions, you can merge fields, andWmsys.wm_concat in Oracle primarily implements row-to-column functionality (it's a matter of using commas to separate a column of a query into a single piece of data) . Wmsys.wm_concat can also be used in conjunction with the over function in addition to being used alone.

Oracle row-to-column operations