Chris Tophski Chris Tophski - 2 months ago 8
SQL Question

How do I join with a table function result in MS SQL Server?

I have something like these 2 tables (but millions of rows in real):

items:
| X | Y |
---------
| 1 | 2 |
| 3 | 4 |
---------

details:
| X | A | B |
-------------
| 1 | a | b |
| 1 | c | d |
| 3 | e | f |
| 3 | g | h |
-------------


I have to aggregate several rows of one table
details
for one row in another table
items
to show them in a GridView like this:

| items.X | items.Y | details.A | details.B |
---------------------------------------------
| 1 | 2 | a, c | b, d |
| 3 | 4 | e, g | f, h |
---------------------------------------------


I already read this and the related questions and I know about GROUP_CONCAT, but I am not allowed to install it on the customer system. Because I don't have a chance to do this natively, I created a stored function (which I'm allowed to create), which returns a table with the columns
X
,
A
and
B
. This function works fine so far, but I don't seem to get these columns added to my result set.

Currently I'm trying to join the function result with a query on
items
, join-criterion would be the
X
-column in the example above. I made a minimal example with the AdventureWorks2012 database, which contains a table function
dbo.ufnGetContactInformation(@PersonID INT)
to join with the
[Person].[EmailAddress]
table on
BusinessEntityID
:

SELECT
[EmailAddress]
-- , p.[FirstName]
-- , p.[LastName]
FROM
[Person].[EmailAddress] e
INNER JOIN
dbo.ufnGetContactInformation(e.[BusinessEntityID]) p
ON p.[PersonID] = e.[BusinessEntityID]


The 2 commented lines indicate, what I try to do in reality, but if not commented, they hide the actual error I get:

Event 4104, Level 16, Status 1, Line 6
The multi-part identifier 'e.BusinessEntityID' could not be bound.


I understand, that during the joining process there is no value for
e.[BusinessEntityID]
yet. So I cannot select a specific subset in the function by using the function parameters, this should be in the join criteria anyway. Additionally I cannot have the function return all rows or create a temporary table, because this is insanely slow and expensive regarding both time and space in my specific situation.

Is there another way to achieve something like this with 2 existing tables and a table function?

Answer

Use Apply

Cross apply is similar to inner join,Outer apply is similar to left join

SELECT
    [EmailAddress]
    -- , p.[FirstName]
    -- , p.[LastName]
FROM
    [Person].[EmailAddress] e
cross apply
    dbo.ufnGetContactInformation(e.[BusinessEntityID]) p
Comments