For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Re: 4. How do I convert rows to columns?
Please READ John's reply and read that LINK.
Then provided DDL statements of your objects (tables) and DML statements of your data (INSERT statements).
Also search this forum on PIVOT questions. You'll find tons.
Hi,
Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.
Explain, using specific examples, how you get those results from that data.
Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
See the forum FAQ:
Do you know how to get the results you want in 1 column, with a separate row for each month? (Actually, that might be a better format for these results. Make sure whoever needs this output really prefers 1 row, with a separate column for each month, before going any farther.)
Assuming you really do want 1 row, then start by getting the results in separate rows for each month, using plain old GROUP BY and AVG.
Include a column that indicates the month (just the month, not including the year). Either
TO_CHAR (rain_date, 'MM') or
EXTRACT (MONTH FROM rain_date) will work.
Once you have a result set like this, you can use the SELECT ... PIVOT feature (it's a feature, not a function) to produce a separate column for each month, as shown in the link John posted in reply #1. Since you already computed the average, the aggregate function used in the PIVOT can be MIN or MAX
If you have trouble, post your code.
The PIVOT operator works better when the grouping is by values in one or more columns. If you need to group by expressions (such as the month extracted from the RAIN_DATE) you may be better off pivoting "manually" - using the pivoting technique that was used for a long time, before the introduction of the PIVOT operator.
In your problem description you mentioned the ten gauges, but you didn't say what role they play. You can average over all gauges and all dates in a month, OR you can average separately for each gauge, and only over all dates in a month (getting a table with ten rows, one for each gauge, and 12 columns, one for each month). In the solution below I adopt the first position: average over all gauges and all dates in a month. If you need averages by gauge AND month, then add GAUGE to the SELECT clause and add GROUP BY GAUGE at the end of the query.
Column names must be known at parse time (unless you are willing to do more complicated things); column names can and should be controlled through your reporting system anyway. I use hard-coded column names JAN, FEB, MAR...
I added just a little bit of test data in a WITH clause. Obviously, that is NOT part of the solution; the query begins after the WITH clause.
On first reading, one might think that the WHERE clause is redundant, since rows where the date is not "this year" don't contribute to the computations anyway. Actually the WHERE clause plays an important role: it rejects rows from "other years" immediately, instead of processing each such "useless" row in 12 CASE expressions. EDIT: Actually this comment doesn't quite apply anymore. Initially I had written the CASE expressions in terms of comparing RAIN_DATE to specific month and year, and I added the WHERE clause for the reason I just explained. (The explanation is incomplete: the main benefit of the WHERE clause is that unneeded rows aren't even read from disk in the first place, if there's an index on RAIN_DATE - even regardless of them being or not being processed in CASE expressions.) But after I added the WHERE clause, I realized I can now simply use EXTRACT (MONTH FROM...) - but now the WHERE clause is in fact needed to ensure the year is the right one. I am not deleting this paragraph since I think the idea is correct, and it may be helpful in other cases.
Also, the condition should be on RAIN_DATE and not on TRUNC(RAIN_DATE) or TO_CHAR(RAIN_DATE) of any kind, so that an index on RAIN_DATE, if it exists (as it should!) can be used. Even avoiding unnecessary function calls is important for performance, but the ability to use an index is enormously more so.
with precipitation (gauge, rain_date, precipitation_amount) as ( select 3, date '2016-12-03', 8.4228 from dual union all select 8, date '2016-12-04', 3.2019 from dual union all select 1, date '2017-01-01', 3.8002 from dual union all select 2, date '2017-01-01', 3.1002 from dual union all select 3, date '2017-01-03', 0 from dual union all select 3, date '2017-01-06', 1.103 from dual union all select 1, date '2017-02-20', 2.3901 from dual union all select 4, date '2017-02-23', 0.932 from dual )select avg(case extract(month from rain_date) when 1 then precipitation_amount end) as jan, avg(case extract(month from rain_date) when 2 then precipitation_amount end) as feb, avg(case extract(month from rain_date) when 3 then precipitation_amount end) as marfrom precipitationwhere rain_date >= trunc(sysdate, 'y') and rain_date < add_months(trunc(sysdate, 'y'), 12);JAN FEB MAR------- ------- -------2.00085 1.66105
with precipitation (gauge, rain_date, precipitation_amount) as ( select 3, date '2016-12-03', 8.4228 from dual union all select 8, date '2016-12-04', 3.2019 from dual union all select 1, date '2017-01-01', 3.8002 from dual union all select 2, date '2017-01-01', 3.1002 from dual union all select 3, date '2017-01-03', 0 from dual union all select 3, date '2017-01-06', 1.103 from dual union all select 1, date '2017-02-20', 2.3901 from dual union all select 4, date '2017-02-23', 0.932 from dual )select avg(case extract(month from rain_date) when 1 then precipitation_amount end) as jan, avg(case extract(month from rain_date) when 2 then precipitation_amount end) as feb, avg(case extract(month from rain_date) when 3 then precipitation_amount end) as marfrom precipitationwhere rain_date >= trunc(sysdate, 'y') and rain_date < add_months(trunc(sysdate, 'y'), 12);
JAN FEB MAR------- ------- -------2.00085 1.66105
Thanks for trying to help folks. I tried reading the links from John but for whatever reason my brain is stuck.
I'll try to make it easier: I created a view with just two columns:
SELECT
RAIN_MONTH, AMOUNT
FROM WEATHER.PRECIP_CURRENTYEAR_AVG
Currently it displays like this:
04/2017 6.465
03/2017 0.2935
The goal is to get it to display the month as columns so it ends up (at the end of the year with 12 columns) like this:
03/2017 04/2017 05/2017
6.465 0.2935 4.40
I tried stuff like this:
SELECT * FROM
(
) PIVOT
( MONTH
FOR AVG(AMOUNT)
IN (01/2017, 02/2017, 03/2017, 04/2017)
)
ORDER BY ASC
I tried stuff like this but I have something wrong:
Thanks again for help
Matthew
What's wrong with the solution Mathguy posted in reply #4? Point out where it's getting the wrong results, and explain what the correct results are in those places. If necessary, post additional sample data.
If you want to use the SELECT ... PIVOT feature, here's one way to implement the plan from reply #3:
WITH rain_month AS( SELECT TO_CHAR (rain_date, 'MON') AS rain_month , AVG (precipitation_amount) AS avg_amount FROM precipitation WHERE rain_date >= TRUNC (SYSDATE, 'YEAR') -- current year only GROUP BY TO_CHAR (rain_date, 'MON'))SELECT *FROM rain_monthPIVOT ( MIN (avg_amount) FOR rain_month IN ( 'JAN' AS jan , 'FEB' AS feb , 'MAR' AS mar -- ... , 'DEC' AS dec ) );
WITH rain_month AS
SELECT TO_CHAR (rain_date, 'MON') AS rain_month
, AVG (precipitation_amount) AS avg_amount
FROM precipitation
WHERE rain_date >= TRUNC (SYSDATE, 'YEAR') -- current year only
GROUP BY TO_CHAR (rain_date, 'MON')
SELECT *
FROM rain_month
PIVOT ( MIN (avg_amount)
FOR rain_month IN ( 'JAN' AS jan
, 'FEB' AS feb
, 'MAR' AS mar
-- ...
, 'DEC' AS dec
;
Output (using the sample data from reply #4):
JAN FEB MAR DEC-------- -------- -------- -------- 2.00085 1.66105
JAN FEB MAR DEC
-------- -------- -------- --------
2.00085 1.66105
Hi, Matthew,
FriendlyTooo wrote:Thanks for trying to help folks. I tried reading the links from John but for whatever reason my brain is stuck.I'll try to make it easier: I created a view with just two columns:SELECT RAIN_MONTH, AMOUNTFROM WEATHER.PRECIP_CURRENTYEAR_AVG
FriendlyTooo wrote:
Post the CREATE VIEW statement.
How do you plan to use the view, aside from this one query?
Would you ever tell a car mechanic just "There's something wrong with my car", without giving any details?
Try to help the people who want to help you. Give some clue about exactly what is wrong. Did it raise an error? Post the complete error message. Did it get the wrong results? Did it get the right results, but in the wrong order? Was it something else?
At the very least, post enough (e.g., CREATE TABLE, INSERT and CREATE VIEW statements) so that people can re-create the problem themselves.
SELECT * FROM(SELECTRAIN_MONTH, AMOUNTFROM WEATHER.PRECIP_CURRENTYEAR_AVG) PIVOT( MONTH
Review the syntax of SELECT ... PIVOT. Compare your query to similar queries that work.
The first thing after 'PIVOT (" is always an aggregate function.
Do you have a column called MONTH, or is the column called RAIN_MONTH?
You can't use functions immediately the FOR keyword.
What are the values that identify the output columns? Is
01/2017 (which is just another way of writing
1 / 2017 or approximately
.0004958 ) one of those values?
)ORDER BY ASC
You need an expression (such as a column name) right before the ASC keyword.
FriendlyTooo wrote:Thanks for trying to help folks. I tried reading the links from John but for whatever reason my brain is stuck.
Nonsense. The link is to post that describes HOW you should ask ANY question on these forums? It wasn't specific to your question but for all questions.
And as you can see Frank already has to ask you again and again to POST your DDL statements of the tables/views you are using in your example.
That's the WHOLE point of the FAQ (Frequently Asked Questions) and the post is a guide on HOW TO ASK A QUESTION on these forums. Very simple Friendly Tooo.
So do what Frank asked
How do you plan to use the view, aside from this one query?"
Apologies as I didn't see that the link John posted was not the "how to ask a question". Disregard this post.
jaramill wrote:FriendlyTooo wrote:Thanks for trying to help folks. I tried reading the links from John but for whatever reason my brain is stuck.Nonsense. The link is to post that describes HOW you should ask ANY question on these forums? It wasn't specific to your question but for all questions.[...]
jaramill wrote:
[...]
Not really. Please read Reply 1 again (to which the OP was referring). Then edit your Reply 9 as appropriate. Cheers, mathguy
mathguy wrote:jaramill wrote:FriendlyTooo wrote:Thanks for trying to help folks. I tried reading the links from John but for whatever reason my brain is stuck.Nonsense. The link is to post that describes HOW you should ask ANY question on these forums? It wasn't specific to your question but for all questions.[...]Not really. Please read Reply 1 again (to which the OP was referring). Then edit your Reply 9 as appropriate. Cheers, mathguy
mathguy wrote:
OOPs! that font is REALLY ...tiny. I thought it was the one for asking a question. I'll update the my reply.
Without rounding
with precipitation as (select trunc(sysdate - (1 + rownum)) rain_date, case when SYS.dbms_random.value(0, 10) > 8 then SYS.dbms_random.value(0, 2) else 0 end as inches from dual connect by level <= 1000)select * from (select to_char(rain_date,'MON') the_month, inches from precipitation where rain_date >= trunc(sysdate, 'YEAR') ) pivot (avg(inches) for the_month in ('JAN' as jan, 'FEB' as feb, 'MAR' as mar, 'APR' as apr, 'MAY' as may, 'JUN' as jun, 'JUL' as jul, 'AUG' as aug, 'SEP' as sep, 'OCT' as oct, 'NOV' as nov, 'DEC' as dec));
with precipitation as (select trunc(sysdate - (1 + rownum)) rain_date,
case when SYS.dbms_random.value(0, 10) > 8
then SYS.dbms_random.value(0, 2)
else 0
end as inches
from dual connect by level <= 1000)
select *
from (select to_char(rain_date,'MON') the_month,
inches
from precipitation
where rain_date >= trunc(sysdate, 'YEAR') )
pivot (avg(inches) for the_month in ('JAN' as jan,
'FEB' as feb,
'MAR' as mar,
'APR' as apr,
'MAY' as may,
'JUN' as jun,
'JUL' as jul,
'AUG' as aug,
'SEP' as sep,
'OCT' as oct,
'NOV' as nov,
'DEC' as dec));
With rounding
with precipitation as (select trunc(sysdate - (1 + rownum)) rain_date, case when SYS.dbms_random.value(0, 10) > 8 then SYS.dbms_random.value(0, 2) else 0 end as inches from dual connect by level <= 1000)select * from (select to_char(rain_date,'MON') the_month, round(avg(inches), 4) avg_inches from precipitation where rain_date >= trunc(sysdate, 'YEAR') group by to_char(rain_date,'MON') ) pivot (max(avg_inches) for the_month in ('JAN' as jan, 'FEB' as feb, 'MAR' as mar, 'APR' as apr, 'MAY' as may, 'JUN' as jun, 'JUL' as jul, 'AUG' as aug, 'SEP' as sep, 'OCT' as oct, 'NOV' as nov, 'DEC' as dec));
round(avg(inches), 4) avg_inches
where rain_date >= trunc(sysdate, 'YEAR')
group by to_char(rain_date,'MON')
pivot (max(avg_inches) for the_month in ('JAN' as jan,