Start a new topic

How can I transponse rows into column

[This topic is migrated from our old forums. The original author name has been removed]

In qmf I can use column usages 'across' . Then I searched for a possibility in db visualizer, I found something but it is very complicated if there are many expressions for a database field. For this query I have 12 'carr' expressions , for row 'flight' if have 155 expressions. I need all informations in one row, so that 'KTLG', 'RATG', 'NR', 'CODE', 'VERS' will not be repeated. Can someone help me? with hilfstabelle as (select rbel_carr_code, rownumber () over (order by rbel_carr_code) as carr from (select distinct rbel_carr_code from nur.vrbel000 where vast_code = '03' and rbel_termin > '2013-02-28') as R ) select b.ktlg_code_ext, a.ratg_ra_nr, a.ratg_versionsnr, b.sang_angebotscode, c.vsan_angebotscode --,min(case when carr = 1 then a.rbel_carr_code end) carr1 --,min(case when carr = 2 then a.rbel_carr_code end) carr2 --,min(case when carr = 3 then a.rbel_carr_code end) carr3 --,min(case when carr = 4 then a.rbel_carr_code end) carr4 ,min(case when carr = 5 then a.rbel_carr_code end) carr5 ,min(case when carr = 6 then a.rbel_carr_code end) carr6 --,min(case when carr = 7 then a.rbel_carr_code end) carr7 --,min(case when carr = 8 then a.rbel_carr_code end) carr8 --,min(case when carr = 9 then a.rbel_carr_code end) carr9 --,min(case when carr = 10 then a.rbel_carr_code end) carr10 ,min(case when carr = 11 then a.rbel_carr_code end) carr11 ,min(case when carr = 12 then a.rbel_carr_code end) carr12 ,a.rbel_flug_nr from nur.vrbel000 a, hilfstabelle h, nur.vxapl900 b, nur.vrapv000 c where a.rbel_carr_code = h.rbel_carr_code and a.vast_code = '03' and a.vast_code = b.vast_code and a.ratg_ra_nr = b.ratg_ra_nr and a.ratg_versionsnr = b.ratg_versionsnr and a.vast_code = c.vast_code and a.ratg_ra_nr = c.ratg_ra_nr and a.ratg_versionsnr = c.ratg_versionsnr and b.ratg_aktuell_kz = 'J' and b.rapo_aender_Kz ^= 'S' group by b.ktlg_code_ext , a.ratg_ra_nr , a.ratg_versionsnr , b.sang_angebotscode , c.vsan_angebotscode , a.rbel_flug_nr with ur KTLG RATG NR CODE VERS CARR5 CARR6 CARR11 CARR12 FLIGHT VER 39735448 4 83493 00100 (null) DE (null) (null) 2156 VER 39735448 4 83493 00100 (null) DE (null) (null) 2157 VER 39735448 4 83493 00100 (null) (null) (null) LH 989 VER 39735448 4 83493 00100 (null) (null) (null) LH 996 VER 44346190 4 83493 00100 (null) (null) (null) LH 1003 VER 44346190 4 83493 00100 (null) DE (null) (null) 2157 VER 44346190 4 83493 00100 (null) DE (null) (null) 6156 VER 44346190 4 83493 00100 (null) (null) (null) LH 996 VER 46813934 3 83493 00100 (null) DE (null) (null) 2156 VER 46813934 3 83493 00100 (null) DE (null) (null) 2157 VER 46813934 3 83493 00100 (null) (null) (null) LH 989 VER 46813934 3 83493 00100 (null) (null) (null) LH 996
1 Comment

[This reply is migrated from our old forums.]

Re: How can I transponse rows into column
Hi, You will need to check with the DB2 community (assuming QMF is an IBM product) how to optimize your query, Once you've determined how to optimize it DbVisualizer will most likely be able to execute the select. Regards Roger