Converting of rows to columns and merging resulting rows

Ole_Marius · March 14th, 2012, 04:01 PM

Hi all,

I have run the following sql:

select Security_ID,
legno,
dollar_dur_yc,
dollar_dur_ytm,
balnomval,
sectype,
max(decode (termunit, '2Y', deltavec) ) "TERMUNIT_2Y",
max(decode (termunit, '5Y', deltavec) ) "TERMUNIT_5Y",
max(decode (termunit, '7Y', deltavec) ) "TERMUNIT_7Y",
max(decode (termunit, '10Y', deltavec) ) "TERMUNIT_10Y",
max(decode (termunit, '15Y', deltavec) ) "TERMUNIT_15Y",
max(decode (termunit, '20Y', deltavec) ) "TERMUNIT_20Y",
max(decode (termunit, '30Y', deltavec) ) "TERMUNIT_30Y",
delta_vector_sum
from
(select secshort as Security_ID,
hk.legno as legno,
doldurdf as dollar_dur_yc,
doldur dollar_dur_ytm,
balnomval,
sectype,
termunit,
round(deltavec, 10) as deltavec,
round(balnomval * deltavec/100, 2) as deltavec_amt,
sum(round(balnomval * deltavec/100, 2)) as delta_vector_sum
from tmsdat.termunits t,
tmsdat.deltavectors d,
tmsdat.securities s,
tmsdat.holkeys hk,
tmsdat.pfcholdings ph,
tmsdat.pfcdates pd,
tmsdat.sectypes st,
tmsdat.keyratcalcs kc,
tmsdat.keyratios k
where t.termunitik = d.termunitik
and d.secik = s.secik
and ph.holkeyik = HK.HOLKEYIK
and ph.pfcdik = pd.pfcdik
and hk.secik = s.secik
and s.secik = k.secik
and s.sectypeik = st.sectypeik
and d.keycalcik = kc.keycalcik
and k.keycalcik = kc.keycalcik
and k.legno = hk.legno
and hk.legno = d.legno
and pd.pfcmik = 281
and hk.secik = 132669
GROUP BY secshort,
hk.legno,
doldurdf,
doldur,
balnomval,
sectype,
termunit,
deltavec)
group by Security_ID,
legno,
dollar_dur_yc,
dollar_dur_ytm,
balnomval,
sectype,
delta_vector_sum

And even if the rows are converted to columns, I still get to many rows, as I get one row for each value of termunits, while I really want to merge all values for each termunit into one line. I.e. I want TWO rows to be returned one for each legno (there are two values for legno, 1 and 2). Gosh, I wish I could show the resulting table, but I had some hard time in pasting this table...

Anyway, if anyone is able to understand my problem and have a solution I would be very thankful! Briefly, with a simple example, I want the result to be:

SecID Legno 2Y 3Y 5Y 7Y
7658194 1 0,5 0,2
7658194 2 0,4 0,1

Instead of:

SecID Legno 2Y 3Y 5Y 7Y
7658194 1 0,5
7658194 2 0,4
7658194 1 0,2
7658194 2 0,1

disel2010 · March 15th, 2012, 06:57 AM

Hi,

in which database do you want to do this?

As far as I know this transposing of rows to columns (or pivoting) is done different in each database.
If you're using Oracle, try to using the "connect by clause".

It should look like the following then:

Code:

SELECT SecID, Legno, 2Y, 3Y, 5Y, 7Y
FROM 
    (SELECT  SecID, Legno,2Y, 3Y, 5Y, 7Y
, row_number() OVER ( PARTITION BY Legno  ORDER BY rownum) rn
    FROM  tmsdat.holkeys )
CONNECT  BY Legno = PRIOR Legno and rn = PRIOR rn+1
START WITH rn = 1
GROUP BY Legno

Ole_Marius · March 20th, 2012, 05:35 AM

Hi disel2010,

Thanks a lot for your reply, I found a way to do this but will of course have look at your suggestion.

Regards,
Ole Marius