Informix SQL SELECT multi-row result - how to present in one row?

Question

Following request:

select s.site_name as site, NVL(ct.calltypename,"<No queue assigned>") as queue,
  AVG(TO_NUMBER(cd.datavalue)) as average, count(distinct cr.callid) as count
from callrecord cr 
left join calldata cd on cd.callid=cr.callid and cd.sitekey=cr.arrivalsite and cr.requeuecount=cd.requeuecount-1 
left join calltypes ct on cr.arrivalsite=ct.sitekey and cr.calltypekey=ct.calltypekey
left join sites s on cr.arrivalsite=s.sitekey
where cd.datakey="key1"
group by site, queue

produce following result:

  site  queue                   average    count
  MSK     <No queue assigned>   3.00       4
  MSK     Sales                 2.00       1                
  MSK     Service               3.63       32 

There may be up to 6 rows in the result. Task is to reformat the output for the application that use SQL to retrieve data. This application can send only one SQL command and can process only one row. So SQL result should look like:

  site1   queue1   average1   count1   site2   queue2   average2  count2 ...
  MSK     Sales    2.00       1        MSK     Service  3.63      32

It is also important that each queue should always appear in the same column; if there is no data, column should be empty, null or zero. I have found some examples for MS SQL, but they do not work in Informix.

Answer 1

I think you can use window functions and conditional aggregation:

with t as (
      < your query here >
     )
select max(case when seqnum = 1 then site end) as site_1,
       max(case when seqnum = 1 then queue end) as queue_1,
       max(case when seqnum = 1 then average end) as average_1,
       . . . 
       max(case when seqnum = 6 then site end) as site_6,
       max(case when seqnum = 6 then queue end) as queue_6,
       max(case when seqnum = 6 then average end) as average_6,
from (select t.*,
             row_number() over (partition by site order by queue) as seqnum
      from t
     ) t;