Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to add Aggregated Transform as a new column Expand / Collapse
Author
Message
Posted Monday, April 28, 2014 7:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 14, 2014 11:27 AM
Points: 3, Visits: 23
Hi,

I have a flat file with 2 colums: TradeID and Nominal. Now I would like to add third column showing the summed nominal (=position) per tradeID. Please see an example attached. What would be the best way to do this?

br,
Jack


  Post Attachments 
Capture.PNG (8 views, 4.22 KB)
Post #1565565
Posted Monday, April 28, 2014 7:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 12,923, Visits: 32,313
the best way is to create a view, which would calculate that information on demand.
Best practice is to never add a column containing agregates in a table row, since it woudl require an update every time data changes.

you could achieve the same thing in line with a user defined function and a calculated column that uses that function, but the view is my recommendation.

;WITH MyCTE([TradeID],[nominal])
AS
(
SELECT 'TRADE1',500 UNION ALL
SELECT 'TRADE1',1000 UNION ALL
SELECT 'TRADE2',2000 UNION ALL
SELECT 'TRADE2',2000
)
SELECT
MyCTE.[TradeID],
MyCTE.[nominal],
Sumz.[position]
FROM MyCTE
INNER JOIN(SELECT [TradeID],SUM([nominal]) As position FROM MyCTE GROUP BY [TradeID]) Sumz
ON MyCTE.[TradeID]=Sumz.[TradeID]



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1565569
Posted Tuesday, April 29, 2014 1:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 14, 2014 11:27 AM
Points: 3, Visits: 23
Thanks a lot Lowell, I'll investigate that. So, you don't think it makes sense to do it in the SSIS package using eg. Aggregate Transformation?
br,
Jack
Post #1565784
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse