DB2 Combine Columns from same table

[bnp1]

Hello.
I have this query:
select distinct a.callid,
case when a.appsequencenbr = 1 then char(a.appid)
else ' '
end as appid1,
case when a.appsequencenbr = 2 then char(a.appid)
else ' '
end as appid2,
case when a.appsequencenbr = 3 then char(a.appid)
else ' '
end as appid3,
case when a.appsequencenbr = 4 then char(a.appid)
else ' '
end as appid4,
case when a.appsequencenbr = 5 then char(a.appid)
else ' '
end as appid5,
case when a.appsequencenbr = 6 then char(a.appid)
else ' '
end as appid6
from call_app_detail a,
(Select b.callid from call_app_Detail b where
b.calldate > '08/21/2006'
and b.appsequencenbr >1) as q1
where a.callid=q1.callid and a.calldate > '08/21/2006' order by a.callid

that produces these results (sample..)
CALLID APPID1 APPID2 APPID3 APPID4 APPID5 APPID6
1481572713 161
1481572713 110
1481574538 150
1481574538 170
1481574657 150
1481574657 170
1481574664 150
1481574664 170
1481574682 150
1481574682 170
1481574691 161
1481574691 170
1481574695 102
1481574695 110



I want to produce a result set that combines the callids so the result
will look like:
CALLID APPID1 APPID2 APPID3 APPID4 APPID5 APPID6
1481572713 161 110
1481574538 150 170
1481574657 150 170
1481574664 150 170
1481574682 150 170
1481574691 161 170
1481574695 102 110


Does anybody know how to do this? Seems like it should be simple, but
I just don't know what I am missing. thanks

 

[blom0344]

Can't test it, try:

select a.callid,
max(case  when a.appsequencenbr = 1 then   char(a.appid)
else ' '
end)as appid1,
max(case  when a.appsequencenbr = 2 then   char(a.appid)
else ' '
end) as appid2,
max(case  when a.appsequencenbr = 3 then   char(a.appid)
else ' '
end) as appid3,
max(case  when a.appsequencenbr = 4 then   char(a.appid)
else ' '
end) as appid4,
max(case  when a.appsequencenbr = 5 then   char(a.appid)
else ' '
end) as appid5,
max(case  when a.appsequencenbr = 6 then   char(a.appid)
else ' '
end) as appid6
  from call_app_detail a,
     (Select b.callid from  call_app_Detail b where
         b.calldate > '08/21/2006'
         and b.appsequencenbr >1) as q1
where a.callid=q1.callid and a.calldate > '08/21/2006'  group by a.callid

Ties Blom