As per the error message, you need to include the {User}.[Id] in your GROUP BY clause.

Simply add it as below:

GROUP BY u.[Id],
         u.[Username]

The "{User} u" alias is unnecessary and I would actually remove it as a best practice.

Hi Gonçalo Soares,

Thanks for your responds.

If i use u.[Id] in Group By, the names will not be concatenate..

Id       Username           Name

1          ABC                 Thani

2          ABC                 Jai

3          ABC                 Saravana

4          XYZ                  Guru

5          XYZ                  Ramesh

6          XYZ                  karthi

Ids are like this, so how can i use "ID" in Group by, if i use "ID" then the result is the same.

Bcz ID is not redundant. What should i do?? 

Try to take aliases, and when you do group by, even use {User}.[Username]

Cheers,

Nuno Verdasca

Wow, what a strange situation you have: duplicate Usernames in your database!

Aside from that, the problem is your join condition inside the STUFF function.

What you want to do is something like ...

WHERE u.[Username] = u1.[Username]

... instead of a join by {User}.[Id], and remove the GROUP BY clause I told you to add earlier.

I share with you two links from StackOverflow and wiseowl:

• https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server
• https://www.wiseowl.co.uk/blog/s2511/stuff-xml.htm

Cheers,

Nuno Verdasca

Hi Thanigai,

SQL won't let you concatenate numbers and strings like that.

You'll have to CAST the User Id to a string, as per Nuno's WiseOwl link:

SELECT ', ' + CAST(u1.[Id] AS VARCHAR(200))

Also, make sure your output structure has at least two Attributes of type Text.

I got it.

Thank you so much Gonçalo Soares and Nuno.

Hmm... tricky.

It would seem that your personal environment is running SQL Server, and your UAT environment is running on Oracle (as per the ORA-00907 error).

As far as I know the STUFF + XML method is only valid for SQL Server databases.

This StackOverflow shows an example on how to perform String concatenation for Oracle databases, and conveniently links to a larger list of examples.

If you need to support two different database engines (for some reason), you would need to maintain two queries, and dynamically select the relevant query based on the result of the GetDatabaseProvider() built-in function.

Alternatively, you can query the entire list of users (preferably using an Aggregate rather than an Advanced Query), and try to perform the match on the application server, rather than the database. Performance-wise, this might not be ideal but if you need to support two different databases I think it would be preferable to keeping two queries.

If you do try this path, I suggest looking at the TextDictionary or HashTable built-in extensions, to avoid filtering/ iterating over the list multiple times.

We can use listagg database function. Below can give you result of 'column into row conversion with comma separated values',

 select listagg(<column_name>,', ') within group(order by <column_name>)  <alias>   from <table_name>;