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

Question

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?

score 6 · Accepted Answer · answered Sep 22 '16 at 08:31

6

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

answered Sep 22 '16 at 08:31

TheGameiswar

27,855
8
56
94

Thanks! This was exactly what I was looking for. – Chris Tophski Sep 22 '16 at 08:35

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

1 Answers1