May 19, 2014 at 2:53 am
hi i have my data coming in like this
ClassName NAV SharesOutstanding
Class A GBP 23704633.79
Class A GBP 20143018.57
but what i want to happen is this
ClassName NAV SharesOutstanding
Class A GBP 23704633.79 20143018.57
how can this be done
May 19, 2014 at 3:28 am
A simple way would be along these lines
😎
USE tempdb;
GO
DECLARE @DSET TABLE
(
ClassName VARCHAR(50) NOT NULL
,NAV DECIMAL(18,2)
,SharesOutstanding DECIMAL(18,2)
);
INSERT INTO @DSET (ClassName,NAV,SharesOutstanding)
VALUES
('Class A GBP',23704633.79,NULL)
,('Class A GBP',NULL,20143018.57)
,('Class B GBP',NULL,10143018.57)
,('Class B GBP',33704633.79,NULL);
SELECT
D.ClassName
,MAX(D.NAV) AS NAV
,MAX(D2.SharesOutstanding) AS SharesOutstanding
FROM @DSET D
CROSS APPLY @DSET D2
WHERE D.ClassName = D2.ClassName
GROUP BY D.ClassName
Results
ClassName NAV SharesOutstanding
------------ ------------ ------------------
Class A GBP 23704633.79 20143018.57
Class B GBP 33704633.79 10143018.57
May 19, 2014 at 4:38 am
hi thanks for that has to be generic.
i will be reading in loats of files at different times with different info
any way to get it to to that
May 19, 2014 at 9:25 am
ronan.healy (5/19/2014)
hi thanks for that has to be generic.i will be reading in loats of files at different times with different info
any way to get it to to that
Now that is an entirely different question :w00t:
Could you please be a little more specific on what you are after?
😎
May 20, 2014 at 8:09 am
basically i read in a text file i do a few things to it get it to write to my output table it comes out like this
PK_ID FundCode ACCOUNTPERIOD ClassName NAV SharesOutstanding
4 IL01 12/31/2013 Class A GBP 23704633.79
5 IL01 12/31/2013 Class A GBP 20143018.57
and i want it like this
PK_ID FundCode ACCOUNTPERIOD ClassName NAV SharesOutstanding
4 IL01 12/31/2013 Class A GBP 23704633.79 20143018.57
any idea whats the best way to do this
May 28, 2014 at 8:07 am
anyone have ideas on how i can combibe data together.
this is how my table is as of right now
FundCode ACCOUNTPERIOD ClassName NAV SharesOutstanding
IL01 31/12/2013 Class A GBP 23704633.79
IL01 31/12/2013 Class A GBP 20143018.57
IL01 31/12/2013 Class I2 GBP 252178460.2
IL01 31/12/2013 Class I2 GBP 210634139.7
IL01 31/12/2013 Class I2 EUR Hedged 651780501.3
IL01 31/12/2013 Class I2 EUR Hedged 657920807.6
IL01 31/12/2013 Class A EUR Hedged 226139324
IL01 31/12/2013 Class A EUR Hedged 232347880.6
IL01 31/12/2013 Class A CHF 111479078
IL01 31/12/2013 Class A CHF 154690048.6
and i want the same table to end up looking like this instead
FundCode ACCOUNTPERIOD ClassName NAV SharesOutstanding
IL01 31/12/2013 Class A GBP 23704633.79 20143018.57
IL01 31/12/2013 Class I2 GBP 252178460.2 210634139.7
IL01 31/12/2013 Class I2 EUR Hedged 651780501.3 657920807.6
IL01 31/12/2013 Class A EUR Hedged 226139324 232347880.6
IL01 31/12/2013 Class A CHF 111479078 154690048.6
May 28, 2014 at 8:21 am
ronan.healy (5/28/2014)
anyone have ideas on how i can combibe data together.this is how my table is as of right now
FundCode ACCOUNTPERIOD ClassName NAV SharesOutstanding
IL01 31/12/2013 Class A GBP 23704633.79
IL01 31/12/2013 Class A GBP 20143018.57
IL01 31/12/2013 Class I2 GBP 252178460.2
IL01 31/12/2013 Class I2 GBP 210634139.7
IL01 31/12/2013 Class I2 EUR Hedged 651780501.3
IL01 31/12/2013 Class I2 EUR Hedged 657920807.6
IL01 31/12/2013 Class A EUR Hedged 226139324
IL01 31/12/2013 Class A EUR Hedged 232347880.6
IL01 31/12/2013 Class A CHF 111479078
IL01 31/12/2013 Class A CHF 154690048.6
and i want the same table to end up looking like this instead
FundCode ACCOUNTPERIOD ClassName NAV SharesOutstanding
IL01 31/12/2013 Class A GBP 23704633.79 20143018.57
IL01 31/12/2013 Class I2 GBP 252178460.2 210634139.7
IL01 31/12/2013 Class I2 EUR Hedged 651780501.3 657920807.6
IL01 31/12/2013 Class A EUR Hedged 226139324 232347880.6
IL01 31/12/2013 Class A CHF 111479078 154690048.6
The issue here is that we can't tell what you have going on. Can you post this with ddl (create table scripts) and sample data (insert statements)?
I am thinking it might be as simple as this but without something real to work with it is hard to say for sure.
select FundCode, ACCOUNTPERIOD, ClassName, MAX(NAV) as Nav, MAX(SharesOutstanding) as SharesOutstanding
FROM SomeTable
group by FundCode, ACCOUNTPERIOD, ClassName
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2014 at 8:30 am
thats does work as far as it select it, but i need it to be commit to the table like that as when i pull the data into an excel file through a query i want it to display it combined.will it get displayed like that in the excel file with the select?
May 28, 2014 at 8:45 am
You can use a staging table where you insert the values in 2 rows and then use the code to insert them into the final table with a single row.
How are you pulling your data from Excel (or any source that you're using)?
May 28, 2014 at 8:45 am
have it sorted cheers. just created a temp table and used insert with your select
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply