OraFAQ Forum: SQL & PL/SQL » dynamic Column header using Pivot operator

Home » SQL & PL/SQL » SQL & PL/SQL » dynamic Column header using Pivot operator (Oracle12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

dynamic Column header using Pivot operator [message #645966]

Thu, 17 December 2015 03:18

ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore

Member

Hi All,

i am working pivot function.

For suppose

CREATE TABLE pivot_test (
id NUMBER,
customer_id NUMBER,
product_code VARCHAR2(5),
quantity NUMBER
);

INSERT INTO pivot_test VALUES (1, 1, 'A', 10);
INSERT INTO pivot_test VALUES (2, 1, 'B', 20);
INSERT INTO pivot_test VALUES (3, 1, 'C', 30);
INSERT INTO pivot_test VALUES (4, 2, 'A', 40);
INSERT INTO pivot_test VALUES (5, 2, 'C', 50);
INSERT INTO pivot_test VALUES (6, 3, 'A', 60);
INSERT INTO pivot_test VALUES (7, 3, 'B', 70);
INSERT INTO pivot_test VALUES (8, 3, 'C', 80);
INSERT INTO pivot_test VALUES (9, 3, 'D', 90);
INSERT INTO pivot_test VALUES (10, 4, 'A', 100);
COMMIT;

now i am having query...

SELECT *
FROM (SELECT product_code, quantity
FROM pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c));

O/P:-

A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
-------------- -------------- --------------
210 90 160

I want output like this..

SUM_QUANTITY_A SUM_QUANTITY_B SUM_QUANTITY_C
-------------- -------------- --------------
210 90 160

here A,B,C are date like 'Dec 2015'

for example :- now i am getting output like 'Dec 2015'_ticket_book

Required output :- ticket_book_'Dec 2015'

Let me know in case of any concerns.

Thanks,
Amit Jain

[Updated on: Thu, 17 December 2015 03:20]

Report message to a moderator

Re: dynamic Column header using Pivot operator [message #645990 is a reply to message #645966]

Thu, 17 December 2015 05:57

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I would like too.
I think Oracle chose the wrong way to name its columns and the other way would be better and even quite obvious.
So you cannot change the way Oracle chooses to name the columns but you can change the display as you want, for instance with SQL*Plus use COLUMN command.

Report message to a moderator

Re: dynamic Column header using Pivot operator [message #645993 is a reply to message #645966]

Thu, 17 December 2015 06:23

Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA

Senior Member

So what prevents you from aliasing columns the way you prefer?

SELECT A_SUM_QUANTITY SUM_QUANTITY_A,
       B_SUM_QUANTITY SUM_QUANTITY_B,
       C_SUM_QUANTITY SUM_QUANTITY_C
FROM (SELECT product_code, quantity
FROM pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c))
/

SUM_QUANTITY_A SUM_QUANTITY_B SUM_QUANTITY_C
-------------- -------------- --------------
           210             90            160

SQL>

SY.

Report message to a moderator

Re: dynamic Column header using Pivot operator [message #646056 is a reply to message #645993]

Fri, 18 December 2015 05:20

ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore

Member

Thanks to all of you.. Smile

Report message to a moderator

Previous Topic:	Converting Excel to csv using PL/SQL
Next Topic:	Same SQL work differently in different SQL Plus

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 27 15:03:28 CDT 2026