Skip to content
Advertisement

Select multiple values from a single underlying values as a table

I have a table from which I select multiple values based on two underlying values in the table

select (1.2 + ca.MaxReturn),
       (1.0 + ca.MaxReturn),
       (1.0 + ca.MaxReturn * 0.50),
       (1.0 + ca.MaxReturn * 0.25),
       (1.0),
       (ca.BufferLevel + (1.0-ca.BufferLevel)/2.0),
       (ca.BufferLevel + 0.0),
       (ca.BufferLevel - 0.1),
       (ca.BufferLevel - 0.2),
       (ca.BufferLevel - 0.3),
       (0.0)
from  Product.Bren
cross apply (select LowStrike-ProtectionPercentage as BufferLevel,
             ParticipationUpside*UpsideCap MaxReturn) ca
where Id = 1

This returns a set of points

(No column name)    (No column name)    (No column name)    (No column name)    (No column name)    (No column name)    (No column name)    (No column name)    (No column name)    (No column name)    (No column name)
1.350000000000  1.150000000000  1.07500000000000    1.03750000000000    1.0 0.930000000 0.860000    0.760000    0.660000    0.560000    0.0

that I want to use to lookup corresponding values in another table

To do that however I need to convert this set of columns into a a single column

i.e.

Range
1.3500
1.1500
1.0750
1.0375  
1.0000
0.9300
0.8600
0.7600
0.6600
0.5600
0.0000

This looks like I need to pivot the result but I cannot see how to. There are loads of (convert columns to rows answers but none seem to answer what I want.

Advertisement

Answer

You can repeat the cross apply:

select v.range
from Product.Bren b cross apply
     (values (b.LowStrike - b.ProtectionPercentage, b.ParticipationUpside * b.UpsideCap
             )
     ) ca(BufferLevel, MaxReturn) cross apply
     (values (1.2 + ca.MaxReturn),
             (1.0 + ca.MaxReturn),
             (1.0 + ca.MaxReturn * 0.50),
             (1.0 + ca.MaxReturn * 0.25),
             (1.0),
             (ca.BufferLevel + (1.0-ca.BufferLevel)/2.0),
             (ca.BufferLevel + 0.0),
             (ca.BufferLevel - 0.1),
             (ca.BufferLevel - 0.2),
             (ca.BufferLevel - 0.3),
             (0.0)
      ) v(range)
where b.Id = 1;
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement