SQL query to get following result

Question

Table :

create table stock(bDate date, packing varchar(100),  Balance number);

insert into stock values(to_date('1-JAN-2019','DD-MM-YY'),'10kg Normal Bags', 60);
insert into stock values(to_date('1-JAN-2019','DD-MM-YY'),'15kg Box', 60);
insert into stock values(to_date('1-JAN-2019','DD-MM-YY'),'Cealing Role',  60);
insert into stock values(to_date('1-JAN-2019','DD-MM-YY'),'Cello Tape', 60);

insert into stock values(to_date('2-JAN-2019','DD-MM-YY'),'10kg Normal Bags',  70);
insert into stock values(to_date('2-JAN-2019','DD-MM-YY'),'15kg Box',70);
insert into stock values(to_date('2-JAN-2019','DD-MM-YY'),'Cealing Role', 70);
insert into stock values(to_date('2-JAN-2019','DD-MM-YY'),'Cello Tape',  70);

insert into stock values(to_date('3-JAN-2019','DD-MM-YY'),'10kg Normal Bags',80);
insert into stock values(to_date('3-JAN-2019','DD-MM-YY'),'15kg Box', 80);
insert into stock values(to_date('3-JAN-2019','DD-MM-YY'),'Cealing Role', 80);
insert into stock values(to_date('3-JAN-2019','DD-MM-YY'),'Cello Tape', 80);

Looks like

Now I want to represent the data from this table it as follow

I tried,

SELECT * FROM
(
  SELECT * FROM stock
)
PIVOT 
(
  MAX(Balance)
  FOR bDate
  IN (to_date('1-jan-2019','DD-MM-YYYY') as "1-JAN-2019", to_date('2-jan-2019','DD-MM-YYYY') as "2-JAN-2019" , to_date('3-jan-2019','DD-MM-YYYY')  as "3-JAN-2019")
)
ORDER BY packing;

I got expected output. But records of future dates such as 4-JAN-2019, 5-JAN-2019 etc will be added dynamically. Then how can I manage it in above query?

I've tried,

SELECT * FROM
(
  SELECT * FROM stock
)
PIVOT 
(
  MAX(Balance)
  FOR bDate
  IN (select distinct(bDate) from stock)
)
ORDER BY packing;

Error :

ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:
Error at Line: 9 Column: 7

How can I achieve this result dynamically with column labels as corresponding date as shown in picture?

Oracle errors come from the Oracle database. That is the only database tag you need. — Gordon Linoff, Jul 30 '19 at 19:47
mysql users may have alternative way to achieve the expected result, hence I tagged them too — अक्षय परूळेकर, Jul 30 '19 at 19:52
Unfortunately, you're not going to be able to dynamically change the number of columns returned using standard SQL (or at least, you can't do this easily). The best alternative is to create a PL/SQL procedure that will dynamically return the desired number of columns. — Josh Eller, Jul 30 '19 at 19:54
see this https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql — parvij, Jul 30 '19 at 19:59

SQL query to get following result

0 Answers0