Click here to Skip to main content
15,908,172 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have an orale query to return rows from 3 table's.
The result set looks like following

table1 CardNO
table2 JobCode
table3 User

CardNO   JobCode   User
1072      CarWash   A
1072      CarWash   B

I want to Concatinate user A and B and return a result like
CardNo    JobCode   User
1072       Carwash   A,B

How can i achieve it by using oracleSQL. Hope u can help me.
Updated 26-Aug-10 1:34am

1 solution

in SQL Server, you should use 'Stuff'.

Select  CardNO As CardNo,
    JobCode As JobCode,
        Select  ', ' + User
            From    table3
            For Xml Path('')
    ), 1, 2, N'')   As User
    From    table1, table2

since I dont know the relations between your tables, this query is going to return two same rows. use 'where' in 'select' inside 'stuff' to filter data.

in Oracle you shoul use Replace.
for Oracle string concating, see this :

Share this answer
anwarntde 28-Aug-10 2:39am    
Reason for my vote of 1
Thnks for ur reply
When I run this query on TOAD it throws an error
That Right paranthesis missing .on the line For xml Path('')
The Zetta 28-Aug-10 9:05am    
Well, thats because the code is in T-SQL (SQL Server language). for Oracle, see the link. the smile thing is a ":" with a "0"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900