1

Using Oracle 18c and given the following table and data:

create table food (type varchar2(20), name varchar2(30));
insert into food values ('fruit', 'apple');
insert into food values ('fruit', 'pear');
insert into food values ('fruit', 'orange');
insert into food values ('veggie', 'peas');
insert into food values ('veggie', 'carrots');
insert into food values ('veggie', 'squash');
insert into food values ('veggie', 'corn');

I want to pivot the results such that, if I query for fruit, I get the columns:

apple pear orange

but if I query for veggie, I get the columns:

peas carrots squash corn

My goal is to create dynamic SQL or PL/SQL such that, given the type, I get certain columns (and the column length can vary as in the two examples above).

How to do this?
That is, need to know how to pivot rows-to-columns where number of columns can be different but finite.

UPDATE - This comes close but the results are in XML format and I just want the name to be columns:

select * from food
  pivot xml (min(type) for name in ( select name from food where type = 'fruit' ));  

OUTPUT -

<PivotSet>
  <item>
    <column name = "NAME">apple</column>
    <column name = "MIN(TYPE)">fruit</column>
  </item>
  <item>
    <column name = "NAME">orange</column>
    <column name = "MIN(TYPE)">fruit</column>
  </item>
  <item>
    <column name = "NAME">pear</column>
    <column name = "MIN(TYPE)">fruit</column>
  </item>
</PivotSet>
4
  • 2
    Have you concidered to do the pivoting/transposing in the application? – Lennart May 1 '20 at 16:55
  • 1
    Is the input ( 'fruit', 'veggie' ) also a variable? Or can it be a static value? (Eg f( 'fruit' ) ) – Michael Kutz May 1 '20 at 17:27
  • @Lennart yes, but I would prefer SQL or PL/SQL if at all possible. – tale852150 May 1 '20 at 17:59
  • @MichaelKutz yes, the application passes ‘fruit’ or ‘veggie ‘ as a variable. – tale852150 May 1 '20 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.