Quick Search:
 
 Oracle PL/SQL: Converting Rows to Columns Jump to:  
Category: >> Oracle PL/SQL >> Converting Rows to Columns  

<< 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;


 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2023 psoug.org