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

by

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

More Related Contents:

  1. Comma separated results in SQL
  2. T-SQL stored procedure that accepts multiple Id values
  3. Understanding PIVOT function in T-SQL
  4. When should I use semicolons in SQL Server?
  5. How do I escape a percentage sign in T-SQL?
  6. Using RegEx in SQL Server
  7. How to get Time from DateTime format in SQL?
  8. How do you count the number of occurrences of a certain substring in a SQL varchar?
  9. Find index of last occurrence of a sub-string using T-SQL
  10. Hidden Features of SQL Server
  11. SQL Server 2008 Empty String vs. Space
  12. How FOR XML PATH(”) works when concatenating rows
  13. How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?
  14. How do I exclude Weekend days in a SQL Server query?
  15. EXEC sp_executesql with multiple parameters
  16. Preserving ORDER BY in SELECT INTO
  17. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  18. What is the Oracle equivalent of SQL Server’s IsNull() function?
  19. Can you call a webservice from TSQL code?
  20. How to add custom attributes to SQL connection string?
  21. SQL Recursive CTE: Finding objects linked by property
  22. Multiple Column Pivot in T-SQL
  23. T-sql – determine if value is integer
  24. Joining multiple tables returns NULL value
  25. Which join syntax is better?
  26. Compute MD5 hash of a UTF8 string
  27. LPAD in SQL Server 2008
  28. How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
  29. How do I remove non-breaking spaces from a column in SQL server?
  30. What are row, page and table locks? And when they are acquired?

Leave a Comment