AD

Transferred to Oracle row

oracle row turn out:

Table structure is as follows:

 Product sales     Quarter 
 Cheese 50 for the first quarter 
 Cheese 60 second quarter 
 50 beers in the second quarter 
 Beer 80 IV quarter 


Want to convert the following formats:

 Product name of the first quarter sales   Second quarter sales in the third quarter sales   IV quarter 
 Cheese           50               60           0              0
 Beer            0               50           0              80

Construction of the table statement:

crate table sales (
product_name varchar2 (10),
sale_total number (5),
quarter varcahr2 (10)
);

Line transferred to the statement, save to the view:

create or replace view v_sales (name, first quarter sales, second quarter sales, sales in the third quarter, fourth quarter sales)
as
select product_name,
sum (decode (quarter, 'the first quarter', sale_total, 0)),
sum (decode (quarter, 'second quarter', sale_total, 0)),
sum (decode (quarter, 'third quarter', sale_total, 0)),
sum (decode (quarter, 'fourth quarter', sale_total, 0))
from sales
group by product_name

Query view, you want to convert the format available:

select × from v_sales

decode syntax explained:

decode (expression, search, result, default)

decode function more expressions and search words, if the match result is returned result; if you do not match, return default value; If no default value, null value is returned.

标签: oracle, name query, table structure, null value, match, syntax, fourth quarter, expression search, expressions, search result, sales group, first quarter sales, second quarter sales
分类: Database
时间: 2010-09-30

相关文章

  1. Transferred to Oracle row, column string in the form of stitching

    Oracle10g provides a new function wmsys.wm_concat, can be transferred to the line after the completion of the ...
  2. oracle row are transferred to the column switch

    oracle row are transferred to the column switch 1 summarizes recent forum to raise this issue a lot of people ...
  3. Oracle row transferred

    oracle row turn out: Table structure is as follows: Name of product sales Quarter Cheese 50 percent in the fir ...
  4. oracle row are transferred to another writing

    This is a tree-structured query using oracle oracle row turn out to achieve a written, The key point is to use ...
  5. Transfer of case out Oracle row

    Extracts from: http://www.eygle.com/archives/2006/09/use_any_function.html Transfer of case out Oracle row 200 ...
  6. oracle row are transferred to collection

    1. Summarizes recent forum to raise this issue a lot of people have a relationship with the ranks of the conve ...
  7. oracle row are transferred to the general realization (pipe function)

    --oracle Rows into columns of common implementation : -- To convert 'A,B,C' : --A --B --C -- Implementation me ...
  8. Oracle row are transferred to the sql statement

    drop table ABC; create table ABC(id number,name varchar2(50),kcName varchar2(50),score number); insert into AB ...
  9. Line transferred to oracle sql decode

    Transferred to a database row Confusion has been transferred to the long line of problems suddenly realized, t ...
  10. (Turn-51cto.com) SQL Server database and the Oracle row turn out a special program description

    This paper mainly describes the yes Oracle SQL Server database and the database row to switch out of a more ty ...
  11. sql transferred in the row (a)

    Had never seen recently how sql, and raid it: Line transferred to the column switch is in the development proc ...
  12. Multi-line functions are transferred to Oracle

    Oracle WMSYS.WM_CONCAT function using multi-line transferred Original data: rank name AA NAME1 AA NAME2 AA NAM ...
  13. Universal view of the line are transferred to Oracle

    Sent off a post before, called the line transferred to the general procedure, http://topic.csdn.net/u/20091019 ...
  14. Oracle row prefetching

    No row prefetching, the data is a client of a spread; And with row prefetching, such as 10, the data will be p ...
  15. oracle row convert col demo

    In oracle 11g change dedicated to the ranks of the keywords, they do not themselves deal with /*************** ...
  16. Batch files will be transferred to oracle Notepad

    1, create a temporary table in Oracle. create table test ( ID number (20), IP varchar (20) ); 2: d plate to cr ...
  17. oracle row turn out collection

    1. Summarizes recent issue of the Forum, many people mention a relationship with the ranks of conversion, so I ...
  18. Bank of China transferred to oracle dynamic sql statement

    create table T_TEST ( CITY VARCHAR2 (255) not null, YEAR NUMBER (4) not null, MONTH NUMBER (2) not null, SELL_ ...
  19. oracle row turn out (fixed lines)

    Such as: decode (ex1, ex2, ex3, ex4); [here ex1, ex3 represents the column, ex2 represents the column value, e ...