Get output in pivot format [Resolved]

Posted by Santosh4u under Oracle on 2/21/2016 | Points: 10 | Views : 1639 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi i have 10 years of record for each month wise ..i want display records in Pivot format.

Ex:
PName Period amount
Prod1--- 01-01-2016 ---10
Prod1--- 01-02-2016 ---11
Prod1--- 01-03-2016 ---12
Prod1--- 01-04-2016 ---13
same prod1 records present for for 10 years.
Prod2--- 01-01-2016 ---12
Prod2--- 01-02-2016 ---13
Prod2--- 01-03-2016 ---14
Prod2--- 01-04-2016 ---15
same prod2 records present for for 10 years.

output should be

PName--Jan 2016-- Feb 2016-- Mar 2016....Apr 16---for 10 years
Prod1----10---------11----------12-----------13-- so on....
prod2-- --12---------13----------14-----------15-- so on...

for few rows i know it's easy we can pivot the columns but for my requirement i need for more than 10 years.
is it possible to get the above output?reply me if it's not possible.
also you can reply me any other table structure/table need to add to achieve this scenario.

even if i am keeping headers in a separate table below whether it's possible.
tbl_Temp
Jan 2016
Feb 2016
Mar 2016 so on..

let me know if need more clarification.
it's really urgent..

Thanks
Santosh

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Professionaluser on: 2/23/2016 [Member] [MVP] Bronze | Points: 50

0	try the below query SELECT PName, SUM([Jan-2016]),SUM( [Feb-2016]), SUM([Mar-2016]) -- add other all columns for each Month-Year FROM (SELECT PName, Period, Amount, to_char(to_date('2011-01-17', 'yyyy-mm-dd'),'Mon-yyyy') as Month_Year FROM TableName) P PIVOT (SUM(Amount) FOR Month_Year IN ([Jan-2016], [Feb-2016], [Mar-2016]))Pvt -- here also add other columns for each Month-Year GROUP BY PName let me know if you are facing any difficulty to run this query Santosh4u, if this helps please login to Mark As Answer. \| Alert Moderator

try the below query

SELECT PName, SUM([Jan-2016]),SUM( [Feb-2016]), SUM([Mar-2016]) -- add other all columns for each Month-Year
FROM (SELECT PName, Period, Amount, to_char(to_date('2011-01-17', 'yyyy-mm-dd'),'Mon-yyyy') as Month_Year FROM TableName) P
PIVOT (SUM(Amount) FOR Month_Year IN ([Jan-2016], [Feb-2016], [Mar-2016]))Pvt  -- here also add other columns for each Month-Year
GROUP BY PName

let me know if you are facing any difficulty to run this query

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 2/21/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

0	@Santosh4u, I am providing you a pointer for the same ( https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1 ) Basically it's the same PIVOT that we use since SQL SERVER 2005 onwards. Please try this and let us show your progress. We will definitely help you based on that. Thanks -- Thanks & Regards, RNA Team Santosh4u, if this helps please login to Mark As Answer. \| Alert Moderator

@Santosh4u, I am providing you a pointer for the same ( https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1 )

Basically it's the same PIVOT that we use since SQL SERVER 2005 onwards. Please try this and let us show your progress.

We will definitely help you based on that.

Thanks

--
Thanks & Regards,
RNA Team

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts