Quick Search:
Oracle PL/SQL Code Library
Find Or Post Oracle Jobs
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
» 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
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
SELECT location ,
WHEN prod_name = 'PIZZA' THEN sales_amt ELSE 0 END  AS pizza ,
WHEN prod_name = 'BURGER' THEN sales_amt ELSE 0 END  AS burger,
WHEN prod_name = 'SALAD' THEN sales_amt ELSE 0 END  AS salad
FROM prod_sales)
GROUP BY location;

Oracle Magazine
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 196 users online    © 2009 psoug.org

Forgot your password?