I have this data, where I want to generate the last row "on the fly" from the first two:

I am trying to subtract the Port & Bench returns and have the difference on the new row. How can I do this?

Here's my code so far:

Select 
date
            Group,
            Code,
          1 yr returnp,
          2 yrs returnp,
          3yrs return
From timetable
union
 Select   
date,
           Group,
           Code,
          1 yr returnb,
          2 yrs returnb,
          3yrs returnb
From timetable

CodePudding user response：

Seems to me that a UNION ALL in concert with a conditional aggregation should do the trick

Note the sum() is wrapped in an abs() to match desired results

Select *
 From  YourTable
Union All 
Select [Group] = 'Diff'
      ,[1yr]   = abs(sum([1yr]  * case when [Group]='Bench' then -1 else 1 end))
      ,[2yrs]  = abs(sum([2yrs] * case when [Group]='Bench' then -1 else 1 end))
      ,[3yrs]  = abs(sum([3yrs] * case when [Group]='Bench' then -1 else 1 end))
      ,[date]  = null
      ,[code]  = null
 from YourTable

Results

Group   1yr   2yrs  3yrs    date        code
Port    19    -15   88      2020-01-01  arp
Bench   10    -13   66      2020-01-01  arb
Diff    9     2     22      NULL        NULL

 

CodePudding user response：

If you know there is always 2 rows, something like this would work

SELECT * FROM timetable
UNION ALL
SELECT
  MAX(1yr) - MIN(1yr),
  MAX(2yrs) - MIN(2yrs),
  MAX(3yrs) - MIN(3yrs),
  null,
  null,
FROM timetable