1. Categories >> Software >> Databases >> SQL PLSQL
  2. Suggest New Category

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.

Question Posted / basha

Answers were Sorted based on User's Feedback



I have a tablle like this: cust acc ----------- a 1 b 2 b 3 c 4 c ..

Answer / bubun4u

select cust, REPLACE(wm_conact(acc), ',', '|') as acc from table
group by cust

Is This Answer Correct ?    8 Yes 1 No

I have a tablle like this: cust acc ----------- a 1 b 2 b 3 c 4 c ..

Answer / phanikumar

select cust,listagg(acc,'|') within group(order by acc) from
tbl group by cust;

Is This Answer Correct ?    6 Yes 0 No

I have a tablle like this: cust acc ----------- a 1 b 2 b 3 c 4 c ..

Answer / kpk

select cust,wmsys.wm_concat(acc) from tbl group by cust;

Is This Answer Correct ?    0 Yes 0 No

I have a tablle like this: cust acc ----------- a 1 b 2 b 3 c 4 c ..

Answer / rajgopal

select cust,wm_concate(acc) from table_name group by cust;

Is This Answer Correct ?    1 Yes 1 No

I have a tablle like this: cust acc ----------- a 1 b 2 b 3 c 4 c ..

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

I have a tablle like this: cust acc ----------- a 1 b 2 b 3 c 4 c ..

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

I have a tablle like this: cust acc ----------- a 1 b 2 b 3 c 4 c ..

Answer / trainedforjob

select cust, wm_conact(acc) as acc from table
group by cust

Is This Answer Correct ?    1 Yes 3 No

Post New Answer

More SQL PLSQL Interview Questions

how are rank and dense rank being alloted for column with same values over a particular column

1 Answers  

Is like operator in sql case sensitive?

0 Answers  

What is the difference between rename and alias?

0 Answers  

What is server name sql?

0 Answers  

what is sub-query? : Transact sql

0 Answers  




what is the difference between truncate and drop statements? : Sql dba

0 Answers  

What is mdb stand for?

0 Answers  

Can primary key be changed?

0 Answers  

How can we make an if statement within a select statement?

0 Answers  

What are the advantages of indexing?

0 Answers  

What is the default value of CHAR type?

3 Answers   Bosch,

In a PL/SQL block,which loop type should be used in a performance point of view & Why (as both loops can do the same task) 1) open - Fetch loop 2) for loop

4 Answers   JDA,

For more SQL PLSQL Interview Questions Click Here
Categories