October 6, 2008 at 1:34 am
All,
I have to convert columns into rows.
Table Structure:
----------------
CREATE TABLE dbo.mgr_calc
(
sID varchar(15) NOT NULL,
sPform varchar(10) NULL,
AsOfDate datetime NOT NULL,
oney_gross float NULL,
one_yr_ann_std_dev float NULL,
one_yr_sharpe_ratio float NULL,
one_yr_up_capture float NULL,
one_yr_down_capture float NULL,
one_yr_downside_dev float NULL,
one_yr_ann_tracking_error float NULL,
one_yr_info_ratio float NULL,
one_yr_batt_avg float NULL,
threey_gross float NULL,
three_yr_ann_std_dev float NULL,
three_yr_sharpe_ratio float NULL,
three_yr_up_capture float NULL,
three_yr_down_capture float NULL,
three_yr_downside_dev float NULL,
three_yr_ann_tracking_error float NULL,
three_yr_info_ratio float NULL,
three_yr_batt_avg float NULL,
fivey_gross float NULL,
five_yr_ann_std_dev float NULL,
five_yr_sharpe_ratio float NULL,
five_yr_up_capture float NULL,
five_yr_down_capture float NULL,
five_yr_downside_dev float NULL,
five_yr_ann_tracking_error float NULL,
five_yr_info_ratio float NULL,
five_yr_batt_avg float NULL,
CONSTRAINT mgr_pe_6233933402
PRIMARY KEY CLUSTERED (sID,sPform,AsOfDate)
)
----------------
Sample Data:
Please refer the attachment.
Expected ouput:
Please refer the attachment.
Note:
I don't know how many records will match for the PID column.
So the Manager list may increase. How should i handle it ?
karthik
October 6, 2008 at 2:16 am
Meantime, i tried the below code.
SELECT sID,isnull(pct_1y_gross,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(one_yr_ann_std_dev,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(one_yr_sharpe_ratio,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(one_yr_up_capture,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(one_yr_down_capture,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(one_yr_downside_dev,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(one_yr_ann_tracking_error,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(pct_3y_gross,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(three_yr_ann_std_dev,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(three_yr_sharpe_ratio,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(three_yr_up_capture,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(three_yr_down_capture,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(three_yr_downside_dev,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(three_yr_ann_tracking_error,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(pct_5y_gross,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(five_yr_ann_std_dev,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(five_yr_sharpe_ratio,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(five_yr_up_capture,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(five_yr_down_capture,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(five_yr_downside_dev,0)
FROM #mgr_calc
UNION
SELECT sID,isnull(five_yr_ann_tracking_error,0)
FROM #mgr_calc
Output:
1X543210
1X543210.01714
1X543210.1
1X543210.185
1X543210.354
1X543210.5228
1X543210.69142
1X543210.86
1X543211
1X543211.3
1X543211.4
1X543213
1X54321-0.1514
1X54321-0.319999
1X54321-0.48857
1X54321-0.65714
1X54321-0.82571
1X435220
1X435220.01714
1X435220.18571
1X435220.35428
1X435220.52285
1X435220.6914
1X435220.86
1X435221
1X435221.3
1X435221.4
1X435223
1X43522-0.151428
1X43522-0.31999
1X43522-0.48857
1X43522-0.65714
1X43522-0.8249
karthik
October 6, 2008 at 2:32 am
I dodn't know whether it is a part of solution or not. But simply i tried the above approach.
Inputs are welcome !
karthik
October 6, 2008 at 4:40 am
Any inputs ?
karthik
October 6, 2008 at 6:37 am
I got struck here.
Output:
1X54321 0
1X54321 0.01714
1X54321 0.1
1X54321 0.185
1X54321 0.354
1X54321 0.5228
1X54321 0.69142
1X54321 0.86
1X54321 1
1X54321 1.3
1X54321 1.4
1X54321 3
1X54321 -0.1514
1X54321 -0.319999
1X54321 -0.48857
1X54321 -0.65714
1X54321 -0.82571
1X43522 0
1X43522 0.01714
1X43522 0.18571
1X43522 0.35428
1X43522 0.52285
1X43522 0.6914
1X43522 0.86
1X43522 1
1X43522 1.3
1X43522 1.4
1X43522 3
1X43522 -0.151428
1X43522 -0.31999
1X43522 -0.48857
1X43522 -0.65714
1X43522 -0.8249
Expected output:
1X54321 01X43522 0
1X54321 0.017141X43522 0.01714
1X54321 0.11X43522 0.18571
1X54321 0.1851X43522 0.35428
1X54321 0.3541X43522 0.52285
1X54321 0.52281X43522 0.6914
1X54321 0.691421X43522 0.86
1X54321 0.861X43522 1
1X54321 11X43522 1.3
1X54321 1.31X43522 1.4
1X54321 1.41X43522 3
1X54321 31X43522 -0.151428
1X54321 -0.15141X43522 -0.31999
1X54321 -0.3199991X43522 -0.48857
1X54321 -0.488571X43522 -0.65714
1X54321 -0.657141X43522 -0.8249
1X54321 -0.82571
karthik
October 6, 2008 at 7:08 am
Karthik, this looks like a prime candidate for a crosstab, but without sample data...
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 6, 2008 at 8:19 am
Greg,
kindly refer the attachment.
karthik
October 6, 2008 at 8:27 am
Karthik, I am talking about sample table structure and sample data with create and insert statements, so anyone trying to help does not have to guess, or spend time making data. If I have missed it on your spreadsheet I am truly sorry, but I sure did not see anything that looked like it.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 6, 2008 at 9:23 am
Table structure:
CREATE TABLE dbo.mgr_calc
(
sID varchar(15) NOT NULL,
sPform varchar(10) NULL,
AsOfDate datetime NOT NULL,
oney_gross float NULL,
one_yr_ann_std_dev float NULL,
one_yr_sharpe_ratio float NULL,
one_yr_up_capture float NULL,
one_yr_down_capture float NULL,
one_yr_downside_dev float NULL,
one_yr_ann_tracking_error float NULL,
one_yr_info_ratio float NULL,
one_yr_batt_avg float NULL,
threey_gross float NULL,
three_yr_ann_std_dev float NULL,
three_yr_sharpe_ratio float NULL,
three_yr_up_capture float NULL,
three_yr_down_capture float NULL,
three_yr_downside_dev float NULL,
three_yr_ann_tracking_error float NULL,
three_yr_info_ratio float NULL,
three_yr_batt_avg float NULL,
fivey_gross float NULL,
five_yr_ann_std_dev float NULL,
five_yr_sharpe_ratio float NULL,
five_yr_up_capture float NULL,
five_yr_down_capture float NULL,
five_yr_downside_dev float NULL,
five_yr_ann_tracking_error float NULL,
five_yr_info_ratio float NULL,
five_yr_batt_avg float NULL,
CONSTRAINT mgr_pe_6233933402
PRIMARY KEY CLUSTERED (sID,sPform,AsOfDate)
)
Sample Data:
insert into #mgr_calc
Select '1','1X54321',0,2,4,5.8,1.7,2,2,1,0.982,0.45,0.69142,0.5228,0.354,0.00321,2,0.01714,-0.1514,
-0.319999,-0.48857,-0.65714,-0.82571
union all
select '1','1X43522',0,2,4,5.9,3.9,2,1,1,0.8921,2,0.6914,0.52285,0.35428,0.0124,1,0.01714,-0.151428,-0.31999,-0.48857,-0.65714,-0.8249
I hope i have given enough information.
karthik
October 7, 2008 at 12:42 am
Any inputs ?
karthik
October 7, 2008 at 6:23 am
Karthik, both your table structure and sample data have errors, at least when I copy and paste. You are trying to create a composite key on a nullable column, which is easily fixed. However, the data does not match up with the table columns. Did you try to copy and paste it yourself, before you posted it?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 7, 2008 at 6:25 am
Oh..Sorry !
Please find the correct one.
CREATE TABLE dbo.mgr_calc
(
sID varchar(15) NOT NULL,
sPform varchar(10) NOT NULL,
AsOfDate datetime NOT NULL,
oney_gross float NULL,
one_yr_ann_std_dev float NULL,
one_yr_sharpe_ratio float NULL,
one_yr_up_capture float NULL,
one_yr_down_capture float NULL,
one_yr_downside_dev float NULL,
one_yr_ann_tracking_error float NULL,
one_yr_info_ratio float NULL,
one_yr_batt_avg float NULL,
threey_gross float NULL,
three_yr_ann_std_dev float NULL,
three_yr_sharpe_ratio float NULL,
three_yr_up_capture float NULL,
three_yr_down_capture float NULL,
three_yr_downside_dev float NULL,
three_yr_ann_tracking_error float NULL,
three_yr_info_ratio float NULL,
three_yr_batt_avg float NULL,
fivey_gross float NULL,
five_yr_ann_std_dev float NULL,
five_yr_sharpe_ratio float NULL,
five_yr_up_capture float NULL,
five_yr_down_capture float NULL,
five_yr_downside_dev float NULL,
five_yr_ann_tracking_error float NULL,
five_yr_info_ratio float NULL,
five_yr_batt_avg float NULL,
CONSTRAINT mgr_pe_6233933402
PRIMARY KEY CLUSTERED (sID,sPform,AsOfDate)
)
Sample Data:
insert into #mgr_calc
Select '1',’Custom’,getdate(),’1X54321',0,2,4,5.8,1.7,2,2,1,0.982,0.45,0.69142,0.5228,0.354,0.00321,2,0.01714,-0.1514,
-0.319999,-0.48857,-0.65714,-0.82571
union all
select '1',’Custom’,getdate(),'1X43522',0,2,4,5.9,3.9,2,1,1,0.8921,2,0.6914,0.52285,0.35428,0.0124,1,0.01714,-0.151428,-0.31999,-0.48857,-0.65714,-0.8249
karthik
October 7, 2008 at 7:36 am
The way you did it is fine, Karthik... only change I would make is to change all the UNION's into UNION ALL for performance and data integrity reasons. UNION does a DISTINCT and that could blow away some of your data.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 7, 2008 at 7:37 am
Oh yeah... almost forgot... you could also use UNPIVOT... look it up. I don't use it... I normally do it the way you did it.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 7, 2008 at 12:18 pm
karthikeyan (10/7/2008)
Please find the correct one.
Karthik. Dude. Listen, its still not right. Your table has 30 columns, and your sample data only has 25 values. I just added 9.999 five times, to try to load it, but there comes another error. You are trying to insert ’1X54321' into the fourth column, which is float. I thought, no problem, I'll just change the datatype, but the column name suggests it should indeed be some kind of numeric column. So, what is ’1X54321', and for some reason, some of your quotes are showing up as an accent grave, or ague, one em, not sure why, or if its just my machine. Try to copy and paste it yourself and let me know what happens, like errors and the like. It is quite possible that I have either lost my mind, or I am so out of practice I no longer know which way is up. If either of those cases is true, I apologize in advance.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply