SQL & PL/SQL

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 mar
from   precipitation
where  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

(

SELECT

RAIN_MONTH, AMOUNT

FROM WEATHER.PRECIP_CURRENTYEAR_AVG

) PIVOT

( MONTH

FOR AVG(AMOUNT)

IN (01/2017, 02/2017, 03/2017, 04/2017)

)

ORDER BY ASC

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  but I have something wrong:

SELECT * FROM

(

SELECT

RAIN_MONTH, AMOUNT

FROM WEATHER.PRECIP_CURRENTYEAR_AVG

) PIVOT

( MONTH

FOR AVG(AMOUNT)

IN (01/2017, 02/2017, 03/2017, 04/2017)

)

ORDER BY ASC

Thanks again for help

Matthew

Hi,

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_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

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, AMOUNT
FROM WEATHER.PRECIP_CURRENTYEAR_AVG

Post the CREATE VIEW statement.

How do you plan to use the view, aside from this one query?

I tried stuff like this but I have something wrong:

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
(
SELECT
RAIN_MONTH, AMOUNT
FROM 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?

FOR AVG(AMOUNT)

You can't use functions immediately the FOR keyword.

IN (01/2017, 02/2017, 03/2017, 04/2017)

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

Post the CREATE VIEW statement.

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.

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

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