May 19, 2014 at 2:53 am
hi i have my data coming in like this
ClassNameNAVSharesOutstanding
Class A GBP23704633.79
Class A GBP20143018.57
but what i want to happen is this
ClassNameNAVSharesOutstanding
Class A GBP23704633.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_IDFundCodeACCOUNTPERIODClassNameNAVSharesOutstanding
4IL0112/31/2013Class A GBP23704633.79
5IL0112/31/2013Class A GBP20143018.57
and i want it like this
PK_IDFundCodeACCOUNTPERIODClassNameNAVSharesOutstanding
4IL0112/31/2013Class A GBP23704633.7920143018.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
FundCodeACCOUNTPERIODClassNameNAVSharesOutstanding
IL0131/12/2013Class A GBP23704633.79
IL0131/12/2013Class A GBP20143018.57
IL0131/12/2013Class I2 GBP252178460.2
IL0131/12/2013Class I2 GBP210634139.7
IL0131/12/2013Class I2 EUR Hedged651780501.3
IL0131/12/2013Class I2 EUR Hedged657920807.6
IL0131/12/2013Class A EUR Hedged226139324
IL0131/12/2013Class A EUR Hedged232347880.6
IL0131/12/2013Class A CHF111479078
IL0131/12/2013Class A CHF154690048.6
and i want the same table to end up looking like this instead
FundCodeACCOUNTPERIODClassNameNAVSharesOutstanding
IL0131/12/2013Class A GBP23704633.7920143018.57
IL0131/12/2013Class I2 GBP252178460.2210634139.7
IL0131/12/2013Class I2 EUR Hedged651780501.3657920807.6
IL0131/12/2013Class A EUR Hedged226139324232347880.6
IL0131/12/2013Class A CHF111479078154690048.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
FundCodeACCOUNTPERIODClassNameNAVSharesOutstanding
IL0131/12/2013Class A GBP23704633.79
IL0131/12/2013Class A GBP20143018.57
IL0131/12/2013Class I2 GBP252178460.2
IL0131/12/2013Class I2 GBP210634139.7
IL0131/12/2013Class I2 EUR Hedged651780501.3
IL0131/12/2013Class I2 EUR Hedged657920807.6
IL0131/12/2013Class A EUR Hedged226139324
IL0131/12/2013Class A EUR Hedged232347880.6
IL0131/12/2013Class A CHF111479078
IL0131/12/2013Class A CHF154690048.6
and i want the same table to end up looking like this instead
FundCodeACCOUNTPERIODClassNameNAVSharesOutstanding
IL0131/12/2013Class A GBP23704633.7920143018.57
IL0131/12/2013Class I2 GBP252178460.2210634139.7
IL0131/12/2013Class I2 EUR Hedged651780501.3657920807.6
IL0131/12/2013Class A EUR Hedged226139324232347880.6
IL0131/12/2013Class A CHF111479078154690048.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