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>
f( 'fruit' )
) – Michael Kutz May 1 '20 at 17:27