OldMcDonald OldMcDonald - 4 months ago 8
SQL Question

Oracle SQL - Rows into Columns

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.

vkp vkp
Answer

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

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