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 many scalar data types are supported in pl/sql?

0 Answers  

What is the fastest way of accessing a row in a table?

5 Answers   TCS,

What is pl sql commands?

0 Answers  

What is record type in pl sql?

0 Answers  

What is int identity in sql?

0 Answers  




What is sqlca in db2?

0 Answers  

What are the different types of sql commands?

0 Answers  

What is count * in sql?

0 Answers  

How do you write a complex sql query?

0 Answers  

How do I truncate a sql log file?

0 Answers  

How many types of indexes are there in sql?

0 Answers  

What is sql and explain its components?

0 Answers  

For more SQL PLSQL Interview Questions Click Here
Categories