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.
Posted
Updated 26-Aug-10 1:34am
v2

1 solution

in SQL Server, you should use 'Stuff'.

SQL
Select  CardNO As CardNo,
    JobCode As JobCode,
    Stuff(
    (
        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 :http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
--------------------
Regards

H.Maadani
 
Share this answer
 
v4
Comments
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