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;