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

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)!

query result with pivot in oracle

1 solution

Solution 1

Add your solution here

Preview 0

query result with pivot in oracle

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us