query result with pivot in oracle

Question

0.00/5 (No votes)

See more:

i have a query like this

SQL

create or replace view test as
select ppo_no,t.upload_month,t.upload_year,t.net_due ,dr,com,(case when t.upload_month ='Mar' then 1
else
(case when t.upload_month ='Apr' then 2
else
(case when t.upload_month ='May' then 3
else
(case when t.upload_month ='Jun' then 4
else
(case when t.upload_month ='Jul' then 5
else
(case when t.upload_month ='Aug' then 6
else
(case when t.upload_month ='Sep' then 7
else
(case when t.upload_month ='Oct' then 8
else
(case when t.upload_month ='Nov' then 9
else
(case when t.upload_month ='Dec' then 10
else
(case when t.upload_month ='Jan' then 11
else
(case when t.upload_month ='Feb' then 12
else 13
end
) end)end)end)end)end)end)end)end)end)end)end) as mm
from
t_replicate_detail t
where  (t.upload_year >= '2007' and t.upload_year <='2007') and (t.upload_month not in ('Jan','Feb'))
UNION
select ppo_no,upload_month,upload_year, net_due,dr,com,
(
case when upload_month ='Jan' then 11
else
(case when upload_month ='Feb' then 12 end)end)
as mm from t_replicate_detail where   upload_month in('Jan','Feb') and upload_year = '2008'
order
by upload_year,ppo_no,mm;

--------------------------------------------------

could any any convert this result like

P.Detail/Mth. MAR APR MAY JUNE JULY AUG SEP OCT NOV DEC JAN FEB Total

B.PENSION
DR
COMM.
MEDICAL
ARREAR
DEDUCTIONS
TDS
RECOVERY

Gross Income (without deductions)
Net Income

Posted 28-Feb-15 1:34am

raikripa

Updated 28-Feb-15 13:30pm

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-03-01T00:33:00

To be able to get each month as a column data, use something like this:

SQL

SELECT *, [Mar] + [Apr] + ... [Feb] AS Total
FROM (
    SELECT t.upload_year, SUM(CASE WHEN t.upload_month ='Mar' AND t.upload_year = '2007' THEN t.net_due ELSE NULL END) AS [Mar],
        SUM(CASE WHEN t.upload_month ='Apr' AND t.upload_year = '2007'  THEN t.net_due ELSE NULL END) AS [Apr],
        SUM(CASE WHEN t.upload_month ='May' AND t.upload_year = '2007'  THEN t.net_due ELSE NULL END) AS [May],
        ...
        SUM(CASE WHEN t.upload_month ='Jan' AND t.upload_year = '2008' THEN t.net_due ELSE NULL END) AS [Jan],
        SUM(CASE WHEN t.upload_month ='Feb' AND t.upload_year = '2008' THEN t.net_due ELSE NULL END) AS [Feb]
    FROM ...
    WHERE t.upload_year IN ('2007','2008')
    GROUP BY t.upload_year
) AS FinancialYearData

Above example shows how to get sum of net_due for each month.

I'd suggest to read this: PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1[^]

Note: i have no idea why do you store numeric data as a string (upload_year)!