Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Converting Rows to Columns Jump to:  
Category: >> Oracle PL/SQL >> Converting Rows to Columns Bookmark and Share

<< lastnext >>

Snippet Name: Converting Rows to Columns

Description: Easy ways to convert rows to columns in Oracle PL/SQL.

Also see:
» Add PSOUG Search to SQL Developer
» Make alternating color table rows auto...
» Convert Seconds to Hours:Minutes:Secon...
» Convert UK Dates To mySQL Format Dates
» Convert miles to feet, feet to miles, ...
» Highlight table rows on rollover
» Converting Rows to Columns
» Output mySQL data in columns
» Dynamically Add/Remove rows in HTML ta...
» TABLE - Multiple Columns
» Database Links: CURRENT_USER
» Instant Test Database with DCBA
» SELECT name columns
» CONVERT
» UPDATE: Update multiple rows
» INSERT: Multiple Column Table Or View ...
» INSERT: Multiple Column Table Or View ...
» Convert text to soundex
» Count rows in all tables
» List tables with more than 'X' rows
» Convert between Decimal, Binary, Octal...
» Convert LONGs to LOBs
» Show info on current context
» Lookup Oracle error messages
» Display and release DBMS_LOCK locks
» Display locks and latches
» Show rollback segment stats
» Show active transactions
» List supported INIT.ORA parameters
» Display database SGA statistics

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: April 04th, 2010

SELECT location , SUM(pizza_amt) AS pizza_sales,SUM(burger_amt) AS burger_sales,SUM(salad_amt) AS salad_sales
FROM (
SELECT location , DECODE(prod_name,'PIZZA',sales_amt,0) pizza_amt,
        DECODE(prod_name,'BURGER',sales_amt,0) burger_amt,
         DECODE(prod_name,'SALAD',sales_amt,0) salad_amt
FROM prod_sales)
GROUP BY location
 
/* An alternate way to decode for changing rows to cols: */
 
SELECT  location,SUM(pizza) AS pizza , SUM(burger) AS burger , SUM(salad) AS salad
FROM (
SELECT location ,
CASE
WHEN prod_name = 'PIZZA' THEN sales_amt ELSE 0 END  AS pizza ,
CASE
WHEN prod_name = 'BURGER' THEN sales_amt ELSE 0 END  AS burger,
CASE
WHEN prod_name = 'SALAD' THEN sales_amt ELSE 0 END  AS salad
FROM prod_sales)
GROUP BY location;


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 66 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?