Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 14th, 2012, 04:01 PM
Registered User
 
Join Date: Mar 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Converting of rows to columns and merging resulting rows

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
 
Old March 15th, 2012, 06:57 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

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
 
Old March 20th, 2012, 05:35 AM
Registered User
 
Join Date: Mar 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Converting rows...

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Rows to Columns crazeydazey SQL Language 6 April 8th, 2009 02:49 AM
Rows 2 Columns kilika Oracle 0 October 14th, 2005 01:16 PM
Columns To Rows alyeng2000 SQL Language 2 March 11th, 2005 03:08 PM
Rows into columns shamsad SQL Language 0 April 7th, 2004 04:39 AM
Rows into columns shamsad Oracle 0 April 7th, 2004 04:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.