Rows to Columns in Sql

Question

I want to get set of mail Ids but in my Query this is not getting parsed properly as single row.Please hlep me to solve this.

    SELECT LISTAGG ( EMAIL) WITHIN GROUP (ORDER BY EMAIL) EMAIL1 FROM USER  
WHERE ID IN (107) GROUP BY EMAIL

Current Output :

[email protected]
[email protected]
[email protected]

Expected Output :

[email protected],[email protected],[email protected]

Because you have `GROUP BY EMAIL` each Email is a group - hence you get a separate row for each. You must ask yourself the question what does the list of EMails apply to ? A Department ? A Salary ....? — Hugh Jones, Oct 28 '13 at 13:58

jpw · Accepted Answer · 2013-10-28T14:03:53.573

3

You're grouping on EMAILwhich is why they end up on different rows. Try grouping by ID instead like:

SELECT LISTAGG ( EMAIL, ',') WITHIN GROUP (ORDER BY EMAIL) EMAIL1 
FROM USER
WHERE ID IN (107) 
GROUP BY ID

For a working example see this SQL Fiddle.

On a side note USERis a reserved word in both ANSI and Oracle SQL and should not be used as a table name.

edited Oct 28 '13 at 14:03

answered Oct 28 '13 at 13:56

jpw

1 Answers1