Hello,
I am fairly new to Oracle PL/SQL.
I have the following schema: http://sqlfiddle.com/#!4/cae7c/2/0
CREATE TABLE products
(product_id NUMBER(5) NOT NULL,
product_name VARCHAR2(10) NOT NULL,
CONSTRAINT product_pk PRIMARY KEY (product_id)
);
CREATE TABLE packages
(package_id NUMBER(5) NOT NULL,
package_name VARCHAR2(10) NOT NULL,
CONSTRAINT package_pk PRIMARY KEY (package_id)
);
CREATE TABLE product_packages
(product_id NUMBER(5) NOT NULL,
package_id NUMBER(5) NOT NULL,
CONSTRAINT product_fk FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT package_fk FOREIGN KEY (package_id) REFERENCES packages(package_id)
);
CREATE TABLE customers
(customer_id NUMBER(10) NOT NULL,
customer_name VARCHAR2(50) NOT NULL,
CONSTRAINT customer_pk PRIMARY KEY (customer_id)
);
CREATE TABLE orders
(order_id NUMBER(5) NOT NULL,
customer_id NUMBER(10) NOT NULL,
product_id NUMBER(5) NOT NULL,
adl_qty NUMBER(5) NOT NULL,
kid_qty NUMBER(5),
status VARCHAR2(10),
CONSTRAINT ord_customer_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
CONSTRAINT ord_product_fk FOREIGN KEY (product_id) REFERENCES products(product_id)
);
INSERT INTO products (product_id, product_name) VALUES (1, 'BARBB');
INSERT INTO products (product_id, product_name) VALUES (2, 'BARHB');
INSERT INTO products (product_id, product_name) VALUES (3, 'BARFB');
INSERT INTO packages (package_id, package_name) VALUES (1, 'BRF');
INSERT INTO packages (package_id, package_name) VALUES (2, 'LNC');
INSERT INTO packages (package_id, package_name) VALUES (3, 'DNR');
INSERT INTO packages (package_id, package_name) VALUES (4, 'KIDBRF');
INSERT INTO packages (package_id, package_name) VALUES (5, 'KIDLNC');
INSERT INTO packages (package_id, package_name) VALUES (6, 'KIDDNR');
INSERT INTO product_packages (product_id, package_id) VALUES (1, 1);
INSERT INTO product_packages (product_id, package_id) VALUES (1, 4);
INSERT INTO product_packages (product_id, package_id) VALUES (2, 1);
INSERT INTO product_packages (product_id, package_id) VALUES (2, 4);
INSERT INTO product_packages (product_id, package_id) VALUES (2, 3);
INSERT INTO product_packages (product_id, package_id) VALUES (2, 6);
INSERT INTO product_packages (product_id, package_id) VALUES (3, 1);
INSERT INTO product_packages (product_id, package_id) VALUES (3, 2);
INSERT INTO product_packages (product_id, package_id) VALUES (3, 3);
INSERT INTO product_packages (product_id, package_id) VALUES (3, 4);
INSERT INTO product_packages (product_id, package_id) VALUES (3, 5);
INSERT INTO product_packages (product_id, package_id) VALUES (3, 6);
INSERT INTO customers (customer_id, customer_name) VALUES (1, 'John Smith');
INSERT INTO customers (customer_id, customer_name) VALUES (2, 'Adam Nash');
INSERT INTO customers (customer_id, customer_name) VALUES (3, 'Brain Tracy');
INSERT INTO orders (order_id, customer_id, product_id, adl_qty, kid_qty, status) VALUES (1, 1, 2, 1, 0, 'DUE OUT');
INSERT INTO orders (order_id, customer_id, product_id, adl_qty, kid_qty, status) VALUES (2, 2, 3, 2, 1, 'ARRIVAL');
INSERT INTO orders (order_id, customer_id, product_id, adl_qty, kid_qty, status) VALUES (3, 3, 3, 1, 2, 'STAY OVER');
Oracle DB version is 11.2.0.1
I am trying to use multiple pivots and this is what i accomplished so far
select * from
(select * from
(select product_id, adl_qty, kid_qty, status,
case
when status = 'DUE OUT' and package_name = 'BRF' then 'BRF'
when status = 'ARRIVAL' and package_name IN ('LNC', 'DNR') then package_name
when status = 'STAY OVER' and package_name IN ('BRF', 'LNC', 'DNR') then package_name
--else package_name
end
adl_package,
case
when status = 'DUE OUT' and package_name = 'KIDBRF' then 'KIDBRF'
when status = 'ARRIVAL' and package_name IN ('KIDLNC', 'KIDDNR') then package_name
when status = 'STAY OVER' and package_name IN ('KIDBRF', 'KIDLNC', 'KIDDNR') then package_name
--else package_name
end
kid_package
from orders
left join product_packages using (product_id)
left join packages using (package_id)
order by status, adl_package)
pivot
(
max(adl_qty)
for adl_package in ('BRF' brf, 'LNC' lnc, 'DNR' dnr)
)
)
pivot
(
max(kid_qty)
for kid_package in ('KIDBRF' kidbrf, 'KIDLNC' kidlnc, 'KIDDNR' kiddnr)
)
order by product_id;
The result is :
| PRODUCT_ID | STATUS | BRF | LNC | DNR | KIDBRF | KIDLNC | KIDDNR |
|---|
| 2 | DUE OUT | 1 | (null) | (null) | (null) | (null) | (null) |
| 2 | DUE OUT | (null) | (null) | (null) | 0 | (null) | (null) |
| 3 | ARRIVAL | (null) | 2 | 2 | (null) | (null) | (null) |
| 3 | ARRIVAL | (null) | (null) | (null) | (null) | 1 | 1 |
| 3 | STAY OVER | 1 | 1 | 1 | (null) | (null) | (null) |
| 3 | STAY OVER | (null) | (null) | (null) | 2 | 2 | 2 |
As you can see there 2 rows for each order_id, I want one row for each order id; like
| PRODUCT_ID | STATUS | BRF | LNC | DNR | KIDBRF | KIDLNC | KIDDNR |
|---|
| 2 | DUE OUT | 1 | (null) | (null) | 0 | (null) | (null) |
| 3 | ARRIVAL | (null) | 2 | 2 | (null) | 1 | 1 |
| 3 | STAY OVER | 1 | 1 | 1 | 2 | 2 | 2 |
Please help me out how I can do this.