Code Samples 
Oracle Converting Rows to Columns
Converting Rows to Columns in Oracle. There are couple of ways you can do it.
Sample Code
CREATE TABLE prod_sales (
location VARCHAR2(30),
prod_name VARCHAR2(20),
sales_amt NUMBER)
Copyright GeekInterview.com
Sample Code
BEGIN
INSERT INTO prod_sales VALUES(
'NY','PIZZA',500);
INSERT INTO prod_sales VALUES(
'NY','BURGER',300);
INSERT INTO prod_sales VALUES(
'NY','SALAD',250);
INSERT INTO prod_sales VALUES(
'NJ','PIZZA',250);
INSERT INTO prod_sales VALUES(
'NJ','BURGER',310);
INSERT INTO prod_sales VALUES(
'NJ','SALAD',395);
INSERT INTO prod_sales VALUES(
'CT','PIZZA',900);
INSERT INTO prod_sales VALUES(
'CT','BURGER',175);
INSERT INTO prod_sales VALUES(
'CT','SALAD',150);
END;
Copyright GeekInterview.com
Sample Code
SELECT * FROM prod_sales
Copyright GeekInterview.com
Sample Code
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
Copyright GeekInterview.com
This can be used as an alternate to decode for changing rows to cols
Sample Code
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;
Copyright GeekInterview.com
Latest Code Samples
Popular Code Samples
Related Code Samples
Comments
