how to add Aggregated Transform as a new column

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply