Code.GeekInterview.com
 
Code Samples Oracle
 

Converting Rows to Columns


Code ResourceAuthor: Jim.Anderson  

Difficulty Level: Intermediate

Published: 16th Jul 2008   Read: 27786 times  

Filed in: Oracle
Add Comment


 

 

Sponsored Links


 

 

Converting Rows to Columns in Oracle. There are couple of ways you can do it.

 


Sample Code
  1. CREATE TABLE prod_sales (
  2.   location VARCHAR2(30),
  3.   prod_name VARCHAR2(20),
  4.   sales_amt NUMBER)
Copyright GeekInterview.com




Sample Code
  1. BEGIN
  2. INSERT INTO prod_sales VALUES(
  3. 'NY','PIZZA',500);
  4. INSERT INTO prod_sales VALUES(
  5. 'NY','BURGER',300);
  6. INSERT INTO prod_sales VALUES(
  7. 'NY','SALAD',250);
  8. INSERT INTO prod_sales VALUES(
  9. 'NJ','PIZZA',250);
  10. INSERT INTO prod_sales VALUES(
  11. 'NJ','BURGER',310);
  12. INSERT INTO prod_sales VALUES(
  13. 'NJ','SALAD',395);
  14. INSERT INTO prod_sales VALUES(
  15. 'CT','PIZZA',900);
  16. INSERT INTO prod_sales VALUES(
  17. 'CT','BURGER',175);
  18. INSERT INTO prod_sales VALUES(
  19. 'CT','SALAD',150);
  20. END;
Copyright GeekInterview.com





Sample Code
  1. SELECT * FROM prod_sales
Copyright GeekInterview.com





Sample Code
  1. SELECT location , SUM(pizza_amt) AS pizza_sales,SUM(burger_amt) AS burger_sales,SUM(salad_amt) AS salad_sales
  2. FROM (
  3. SELECT location , DECODE(prod_name,'PIZZA',sales_amt,0) pizza_amt,
  4.                   DECODE(prod_name,'BURGER',sales_amt,0) burger_amt,
  5.                                   DECODE(prod_name,'SALAD',sales_amt,0) salad_amt
  6. FROM prod_sales)
  7. GROUP BY location
Copyright GeekInterview.com



This can be used as an alternate to decode for changing rows to cols


Sample Code
  1. SELECT  location,SUM(pizza) AS pizza , SUM(burger) AS burger , SUM(salad) AS salad
  2. FROM (
  3. SELECT location ,
  4. CASE
  5. WHEN prod_name = 'PIZZA' THEN sales_amt ELSE 0 END  AS pizza ,
  6. CASE
  7. WHEN prod_name = 'BURGER' THEN sales_amt ELSE 0 END  AS burger,
  8. CASE
  9. WHEN prod_name = 'SALAD' THEN sales_amt ELSE 0 END  AS salad
  10. FROM prod_sales)
  11. GROUP BY location;
Copyright GeekInterview.com



Next Article: Oracle Database Growth Report


 

Latest Code Samples

 

Popular Code Samples

 

Related Code Samples

 

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    



Comments

how can you do this exactly in c#
Comment posted by: nihir on 2010-07-15T16:11:15
Very nice article. Keep the good work going.
Comment posted by: krishan on 2010-05-21T05:30:53

Popular Coders

# Coder NameHits
1. jamesravid37426
2. krishnaindia200731187
3. Jim.Anderson27787
4. Mohit Sharma20055
5. Beena13467
6. jainsourabh28639
7. Satheesh5680
8. SriramKrishna4771

Active Coders

Refined Tags