sqlsql-serveruser-defined-functions

Use a table-valued function in a SELECT column list

I understand that a table-valued function is designed to appear in the FROM clause, but is there a way to include it in the column list, viz:

SELECT
    x.a,
    x.b,
    FN(x.c),
    x.d
FROM x;

... where the expected output might be something like the following?

x.a x.b fn_col_1 fn_col_2 fn_col_3 x.d
Solution

  • You can cross apply a TVF to a regular table e.g.

    SELECT
    x.a
    , x.b
    , x.d
    , y.*
FROM x
CROSS APPLY FN(x.c) y;