I have a tablle like this:
cust acc
-----------
a 1
b 2
b 3
c 4
c 5
c 6
I Want below o/p:
cust acc
---------------
a 1
b 2|3
c 4|5|6
Please any one can you have any ideas share me.
I have urgent requirement.
Answers were Sorted based on User's Feedback
Answer / bubun4u
select cust, REPLACE(wm_conact(acc), ',', '|') as acc from table
group by cust
Is This Answer Correct ? | 8 Yes | 1 No |
Answer / phanikumar
select cust,listagg(acc,'|') within group(order by acc) from
tbl group by cust;
Is This Answer Correct ? | 6 Yes | 0 No |
Answer / kpk
select cust,wmsys.wm_concat(acc) from tbl group by cust;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / rajgopal
select cust,wm_concate(acc) from table_name group by cust;
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / dinesh
select cust, listagg(acc,'|') WITHIN GROUP (ORDER BY ACC )as ACC from cust1 group by cust;
This LISTAGG Function is the concept of 11g release2. and
this is the best answer for this question.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / prathibha
select customer_id,
MAX(CASE WHEN RNK MOD 8 = 1 THEN ACCOUNT_NO ELSE '' END) ||
MAX(CASE WHEN RNK MOD 8 = 2 THEN ',' || ACCOUNT_NO ELSE '' END) ||
MAX(CASE WHEN RNK MOD 8 = 3 THEN ',' || ACCOUNT_NO ELSE '' END)
AS ACCOUNT_NO
FROM
(
select customer_id,account_no, rank() over (partition by customer_id order by account_no) as rnk
from customer_account ) TEMP
GROUP BY 1
The above query is tested and it works.
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / trainedforjob
select cust, wm_conact(acc) as acc from table
group by cust
Is This Answer Correct ? | 1 Yes | 3 No |
Why does %isopen return false for an implicit cursor?
What are reports usually used for?
what is SCALAR Queries?
what are local and global variables and their differences? : Sql dba
What is keys and its types?
how to rename an existing table in mysql? : Sql dba
What is function and procedure?
What is self-join and what is the requirement of self-join?
Is sql similar to python?
how to create temparary sequence
Table Order_Name has a column Order_Date which gives the date & Time at which the order is passed.Find the table to write a query to find out the latest order.
what are the join types in tsql? : Transact sql