Oracle SQL result in multiple columns

Question

I have a SQL query of a single column that places the output in multiple rows. For instance:

Select distinct academic_year from AFF_EVENT_V
Order by academic_year;

This results in the following:

I'd like the results to appear in multiple (let's say 5) "columns". I'm looking for results something like this:

2002-2003     2003-2004     2004-2005     2005-2006     2006-2007
2007-2008     2008-2009     2009-2010     2010-2011     2011-2012
etc.

How can I structure a Query to result in this output?

Gordon Linoff · Accepted Answer · 2018-07-11 18:41:12Z

3

One method is conditional aggregation:

select max(case when mod(seqnum, 5) = 0 then academic_year end),
       max(case when mod(seqnum, 5) = 1 then academic_year end),
       max(case when mod(seqnum, 5) = 2 then academic_year end),
       max(case when mod(seqnum, 5) = 3 then academic_year end),
       max(case when mod(seqnum, 5) = 4 then academic_year end)
from (select academic_year, row_number() over (order by academic_year) - 1 as seqnum
      from AFF_EVENT_V
      group by academic_year
     ) a
group by floor(seqnum / 5);

answered Jul 11, 2018 at 18:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3138025 Over a year ago

Thanks Gordon. That gave exactly the response I was looking for in SQL Developer. Do you happen to know much about Oracle Apex? When I put your suggestion as the SQL for a report I get an error in Apex 5: Column name "MAX(CASEWHENMOD(SEQNUM,5)=0THENACADEMIC_YEAREND)" is invalid for the SQL query. Make sure that you use unique alias names for your columns.

Littlefoot Over a year ago

Just create an alias for each of those "MAX" values, such as "dummy" value_1, value_2, ..., value_5

user3138025 Over a year ago

Yup. Realized that as soon as I pressed the button. :-) I just added "C1" and "C2" after the last parenthesis of each line. Thanks again. I appreciate it!

Collectives™ on Stack Overflow

Oracle SQL result in multiple columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related