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.

That's the whole point... PIVOT itself is an aggregate function; it is often used with no aggregation (with just one value to be pivoted), and then you can use max() or min() as vkp has shown in his solution. PIVOT wasn't created for transposing tables; it was created for situations where you may have numbers (dollar amounts etc.) in the "text" column, with several rows per id - language combination, and pivoting would be used to add those numbers up, or find the average, etc. So - when you use PIVOT just to transpose a table, you still must use max() or min(). — user5683823
– user5683823, Commented Jul 25, 2016 at 13:36

Vamsi Prabhala · Accepted Answer · 2016-07-26 13:24:13Z

2

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))

edited Jul 26, 2016 at 13:24

answered Jul 25, 2016 at 13:31

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

4 Comments

user5683823 Over a year ago

I believe this will put the column names in double-quotes. That can be avoided (if desired) by including aliases for each column in the list, in the for language in ( ... ) clause.

Vamsi Prabhala Over a year ago

you are correct (column names would be in single-quotes though without the aliases).i edited the answer.

OldMcDonald Over a year ago

This works, thank you. However, 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. Could you provide an example for that as well?

OldMcDonald Over a year ago

Thanks, it worked perfectly. However, I'm unable to "unpivot" the table, what am I doing wrong? SELECT * FROM tablename unpivot (text for language in ('english', 'german' ))

Collectives™ on Stack Overflow

Oracle SQL - Rows into Columns

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related