SQLPlus results in one line

Question

I have an user table where I am looking for user ids with name john

select USER_ID from user where USER_ID like '%john%';

I get

USER_ID ------- john1 john2 john3

Is there a way I can display that information back from SQLPlus as a single line? like

john1, john2, john3

sql*plus doesn't have this kind of functionality, you have to pivot your data yourself, using listagg or pivot — Kirill Leontev
– Kirill Leontev, Commented Sep 15, 2014 at 23:29

Justin Cave · Accepted Answer · 2014-09-15 23:30:02Z

Assuming that you are using 11.2 (the oracle11g tag could also mean 11.1)

select listagg( user_id, ',' ) within group (order by user_id ) from user where user_id like '%john%';

For a canonical list of the various string aggregation techniques that are available in different versions of Oracle, I'd recommend Tim Hall's article on string aggregation techniques.

user1261620 · Accepted Answer · 2014-09-15 23:27:45Z

0

Check out the pivot queries in Oracle 11g. http://www.oracle-developer.net/display.php?id=506

answered Sep 15, 2014 at 23:27

user1261620

3872 gold badges5 silver badges13 bronze badges

Collectives™ on Stack Overflow

SQLPlus results in one line

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related