How to get all usersnames in a single sql row

Question

Possible Duplicate:
Is there an Oracle SQL query that aggregates multiple rows into one row?

i have the below output for my sql query to get the users details from oracle table

   Select distinct userid from Scope1

USERID 
Rakesh 
Admin

i want the output to be as below so that i can do my queries even easier. please help me.

USERID
'Rakesh','Admin'

Thanks

score 1 · Answer 1 · answered Nov 02 '12 at 08:22

1

Try using LISTAGG, Tim Hall has excellent documentation on this here

   select LISTAGG(''''||userid||'''', ',')
   WITHIN GROUP (ORDER BY userid) userid
   from Scope1

SQL Fiddle Demo

answered Nov 02 '12 at 08:22

Jacob

14,463
65
207
320

this works for Oracle 11G but i use Oracke 10g, any alternative please . thanks – Rakesh Nov 02 '12 at 09:57

score 0 · Answer 2 · answered Nov 02 '12 at 07:15

0

You need a function that will do a loop in your query. The function will return a comma seperated value of the data you need. Something like:

str1 := '';
for rec in (select distinct userid from Scope1) 
loop
  str1 := str1 || rec.userid || ', ';
end loop;
str1 := substr(str1, 1, length(str1) - 2);

answered Nov 02 '12 at 07:15

vkamayiannis

745
3
13

it is saying it as an invalid query – Rakesh Nov 02 '12 at 07:30
you should put that in a function: create or replace function fnc as.... – vkamayiannis Nov 02 '12 at 08:17
check also this: http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_5009.htm – vkamayiannis Nov 02 '12 at 08:19

TechDo · Answer 3 · 2012-11-02T08:00:27.430

0

Below query selects the data in comma separated format:

SELECT 
   RTRIM (XMLAGG (XMLELEMENT (E, userid || ',')).EXTRACT ('//text()'), ',') AS UserID
FROM Scope1 ;

How about using wm_concat:

SELECT wm_concat(''''||userid||'''') FROM Scope1;

edited Nov 02 '12 at 08:00

answered Nov 02 '12 at 07:16

TechDo

18,398
3
51
64

thanks bro it worked but i also wanted them with '' like 'Rakesh', 'Admin' – Rakesh Nov 02 '12 at 07:53
Please check the answer altered. – TechDo Nov 02 '12 at 08:01
should it be SELECT wm_concat(''''||userid||'''') (XMLAGG (XMLELEMENT (E, userid || ',')).EXTRACT ('//text()'), ','); if so it is not working brother – Rakesh Nov 02 '12 at 08:05
`SELECT wm_concat(''''||userid||'''') FROM Scope1;` is working in oralce 11G. Which is your oracle version? – TechDo Nov 02 '12 at 08:51
okay... Please check the link: http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php – TechDo Nov 02 '12 at 10:21

score 0 · Answer 4 · answered Nov 02 '12 at 07:19

0

Take a look at the inbuilt LISTAGG function:

http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions087.htm

answered Nov 02 '12 at 07:19

cagcowboy

30,012
11
69
93

How to get all usersnames in a single sql row

4 Answers4