Curious question

[LarrySteele]

Background first...

I had a table with fields such as

PERSON_ID    V
0001         A1
0001         A2
0001         A3
0002         A1
0002         B1
0003         A1
0003         A3
0003         B1

The desired output was

PERSON_ID    V
0001         (A1,A2,A3)
0002         (A1,B1)
0003         (A1,A3,B1)

I searched and found a number of solutions available for v 8.2 and higher using various XML related functions. We have 8.1.5 and sure enough the functions didn't appear to work right.

Since the output was going to Microsoft Access I chose to just let the application concatenate the values. Quick, efficient, and works well.

Now, the question...

On one of the websites I visited, a poster was adamant that data manipulation like this should be handled by the client application, not the db engine.

I've never heard anything like this before and wanted to see what others think - especially DBA's. Should I be concerned with amount of work I put on the db engine versus the amount I put on the client application? If yes, what's a good rule of thumb?

 

[LennyKhiger]

Number of LEFT JOIN equal Maximum(depth) - 1

Lenny

 

[LennyKhiger]

Select p1.person_id, 
Min('(' || p1.v   
    || ifnull(', ' || p2.v, '')
    || ifnull(', ' || p3.v, '') || ')') as V
from persons p1 left join persons p2
On p1.person_id = p2.person_id    
and p2.v > p1.v
left join persons p3 
On p2.person_id = p3.person_id    
and p3.v > p2.v
Group by p1.person_id 
Order by p1.person_id

Lenny

 

[LennyKhiger]

This query tested by me today.
Same result, online, cursor dosn't need.
You can use it, if you want.

with Persons (person_id, v) as
(
select varchar('0001', 50), varchar('A1', 50)
from sysibm.sysdummy1 
union all
select '0001', 'A3'
from sysibm.sysdummy1 
union all
select '0002', 'A1'
from sysibm.sysdummy1 
union all
select '0003', 'A1'
from sysibm.sysdummy1 
union all
select '0003', 'A3'
from sysibm.sysdummy1 
union all
select '0003', 'B1'
from sysibm.sysdummy1 
union all
select '0021', 'C5'
from sysibm.sysdummy1 
union all
select '0021', 'C7'
from sysibm.sysdummy1 
union all
select '0001', 'A2'
from sysibm.sysdummy1 
union all
select '0017', nullif('', '')
from sysibm.sysdummy1 
union all
select '0002', 'B1'
from sysibm.sysdummy1 
)
Select p1.person_id, 
Nullif(Min('(' || ifnull(p1.v, '')         || ifnull(', ' || p2.v, '')
               || ifnull(', ' || p3.v, '') || ifnull(', ' || p4.v, '') || ')'), '()') as V
from persons p1 left join persons p2
On p2.person_id = p1.person_id    
and p2.v > p1.v
left join persons p3 
On  p3.person_id = p2.person_id    
and p3.v > p2.v
left join persons p4 
On  p4.person_id = p3.person_id    
and p4.v > p3.v
Group by p1.person_id 
Order by p1.person_id

Result:

PERSON_ID V
0001 (A1, A2, A3)
0002 (A1, B1)
0003 (A1, A3, B1)
0017
0021 (C5, C7)

Lenny

 

[mikrom]

Hi Lenny,
The both queries you posted last work on my iSeries too.