April 06, 2007

Represent Rows as Single Column in DB2

select ifnull(virtual.Q1, '') || ifnull(virtual.Q2, '') || ifnull(virtual.Q3, '') || ifnull(virtual.Q4, '') || ifnull(virtual.Q5, '') 
|| ifnull(virtual.Q6, '') || ifnull(virtual.Q7, '') || ifnull(virtual.Q8, '') || ifnull(virtual.Q9, '')as sentence from (

SELECT hex(sst.sst_scs_tk), 
       MAX(CASE WHEN sst.sst_ent_seq_nu = 1
           THEN sst.sst_tx END) AS Q1,
       MAX(CASE WHEN sst.sst_ent_seq_nu = 2
           THEN sst.sst_tx END) AS Q2,
       MAX(CASE WHEN sst.sst_ent_seq_nu = 3
           THEN sst.sst_tx END) AS Q3,
       MAX(CASE WHEN sst.sst_ent_seq_nu = 4
           THEN sst.sst_tx END) AS Q4,
 MAX(CASE WHEN sst.sst_ent_seq_nu = 5
           THEN sst.sst_tx END) AS Q5,
 MAX(CASE WHEN sst.sst_ent_seq_nu = 6
           THEN sst.sst_tx END) AS Q6,
 MAX(CASE WHEN sst.sst_ent_seq_nu = 7
           THEN sst.sst_tx END) AS Q7,
 MAX(CASE WHEN sst.sst_ent_seq_nu = 8
           THEN sst.sst_tx END) AS Q8,
 MAX(CASE WHEN sst.sst_ent_seq_nu = 9
           THEN sst.sst_tx END) AS Q9
FROM sst
where sst.sst_scs_tk in (
 select spa.SPA_SCS_TK from spa where spa_per_tk = x'AAEB98F45CA740FF'  
)
GROUP BY sst.sst_scs_tk
) as virtual