Concatenating the columns of diff rows while selecting

gauravgupta2808 · Posted: Mon Oct 29, 2007 10:35 pm

Hi,

I have a data like this in

dick scherrer · Posted: Tue Oct 30, 2007 1:34 am

Hello,

What should happen when there 800 rows of the same sort/account values?

What should happen when there are multiples of the same Fname/Sname? Should duplicates be discarded?

gauravgupta2808 · Posted: Tue Oct 30, 2007 4:43 am

Hi,

In an ideal scenario, there will not be mor ethan 5-6 Sortcode & acoount

There will be no similar FName & SName,,, if they do occur, duplicates are not discarded

stodolas · Posted: Tue Oct 30, 2007 7:34 am

Ideal scenarios don't happen.

What about an account held by Albert Smith and his son Albert Smith Jr and his grandson Albert Smith III. Maybe they were got busy at a young age and there is a great grandson named Albert Smith IV?

gauravgupta2808 · Posted: Tue Oct 30, 2007 2:11 pm

In this case, if they hold sortcode 10001 & Account 20001 then it should display

10001 20001 Albert Smith & Albert Smith Jr & Albert Smith III

If great grandson is also there, then:

10001 20001 Albert Smith & Albert Smith Jr & Albert Smith III & Albert Smith IV

PS: The order in which the names are displayed doesnot matter.

stodolas · Posted: Tue Oct 30, 2007 5:10 pm

You can't do that on a select then. Even if you could, if someone had to come back and do maintenance on it, they would want to strangle the person who wrote it. You should do post processing through COBOL or something.

Setup your query with an order by Sort, Account then loop through it using STRING.

zero · New User Joined: 01 Dec 2007 Posts: 21 Location: Hyderabad

Hi,
if there are only two records for any SORT and ACCOUNT combination, we can use the following query :

SELECT A.SORT, A.ACCOUNT, CONCAT(CONCAT(CONCAT(A.FNAME,A.LASTNAME), '&'),CONCAT(B.FNAME,B.LASTNAME))
FROM SILLY2 A, SILLY2 B
WHERE A.SORT = B.SORT
AND A.ACCOUNT = B.ACCOUNT
AND A.FNAME <> B.FNAME
ORDER BY A.FNAME, A.LASTNAME
Fetch first 1 row only;