sql server - Possible to store value of one select column and use it for the next one?

Question

Is it possible to store or cache values that are part of one select column and then be used for the next one? For example,

select 
       FirstColumn = (complex query returns a value based on ThirdColumn),
       SecondColumn = (uses the same value returned from above + does some additional calculations.)
from SomeTable

Is it possible to do something like that so I don't have to write the same complex query twice?

Answer 1

You need CROSS APPLY here, it can refer to outer references, no annoying subqueries or CTEs needed:

select col1, col2
from table1 as outer_table

-- can also have multi-row values
cross apply (values (complex_expression_1) ) as v1 (col1)
cross apply (values (expression_referring_to_col1) ) as v2 (col2)

-- alternate syntax, select without from returns a single row
cross apply (select complex_expression_1 as col1 ) AS v1
cross apply (select expression_referring_to_col1 as col2 ) as v2

-- you can also do anything you like in there, can be one or multiple rows
cross apply (
    select complex_expression_1 as col1 
    from othercomplexjoin as o
    where o.join_column = outer_table.join_column
) AS v1

Some more tricks you can do with APPLY:

1. Top 1 per group of child table:

A classic solution to the "top 1 per group" is to use row_number(). This can often result in huge scans, especially when the number of distinct outer values is small relative to the child table.

select
    o.id,
    lastPayment.Date
from order_header as o
join
( select *, row_number() over (partition by order_id order by date desc) as rn
 from payments
) as lastPayment on ...
where lastPayment.rn = 1

Instead we can do:

select
    o.id,
    lastPayment.Date
from order_header as o
cross apply
( select top (1) *
 from payments as p
 where p.order_id = o.id
 order by date desc
) as lastPayment

Note: OUTER APPLY conceptually replaces a left join, i.e. returns nulls instead of no rows.

---

2. Unpivoting

select
    o.id,
    customer.*
from order_header as o
cross apply ( values    -- This returns two rows for every order_header
    ( 'DeliveryCustomer', o.deliveryCustomer ),
    ( 'billingCustomer', o.billingCustomer )
) as customer (type, name)

---

3. Exploding out a row a variable number of times:

Say we want to take an amount, and split it into different rows. If the amount <= 50 then one row of amount, if > 50 then two rows, one of 50 and one of the rest:

select t.id, v.amount
from table as t
cross apply (
    select case when amount > 50 then 50 else amount end as amount
    union all
    select amount - 50   -- note this row will not appear if amount < 50
    where amount > 50
) v