Oracle SQL - Rows into Columns

Question

I was wondering if it is possible in to display some rows as columns in Oracle 11g.

I want to the following

id      language     text
--------------------------
1       english      some example
1       german       an example
2       english      test
2       german       test123

to be displayed like this:

id      english        german
---------------------------------
1       some example   an example
2       test           test123

I tried using pivot but wasn't able to figure out how to handle it correctly, since pivot requires an aggregate function.

Answer 1

Assuming you have one text per language per id, you can use max or min on the text column in pivot.

select * from tablename
pivot
(max(text) for language in ('english' as ENGLISH,'german' as GERMAN))

EDIT: Based on OP's comment I'm having trouble figuring out how the SELECT query works if I don't want to select everything in that case. SELECT something, text FROM tablename or similar is not working

Remember you should select all the columns you are pivoting.

select * from 
(select text,language,id,something from tablename) t
pivot
(max(text) for language in ('english' as ENGLISH,'german' as GERMAN))

Edit: To unpivot use

select * 
from (
select * from 
(select text,language,id,something from tablename) t
pivot
(max(text) for language in ('english' as ENGLISH,'german' as GERMAN))
) p 
unpivot (text for language in (ENGLISH, GERMAN))