sqlsql-serverquerying

How to sum the amount and merge the offers in a single row?


I have table with discount transactions data as below:

TransactionID DiscountAmount DiscountOffer
S011-T012 50 Jun-21
S011-T012 25 ManagerDisc
S011-T025 15 Jul-21

I need to create a table in a way that for a single transaction, Discount amount is summed up and Discount offer is shown side-by-side (maybe with a delimiter between them). For example:

TransactionID DiscountAmount DiscountOffer
S011-T012 75 Jun-21 / ManagerDisc
S011-T025 15 Jul-21

I'm able to sum the discount amounts but having a hard time merging the offers.

Can someone provide any tips on how to achieve this?


Solution

  • You can use GROUP BY, as in:

    select
      TransactionID,
      sum(DiscountAmount) as DiscountAmount,
      string_agg(DiscountOffer, ' / ') as DiscountOffer
    from t
    group by TransactionID