Using Cross Apply - How to Join Tables to Cross Apply Join

Question

How do I join back onto the value that is being returned as value from a CROSS APPLY?

The below code is taking a value stored as a CSV in a single column and splitting it out to be returned as a table using STRING_SPLIT(). I simply want to JOIN back on those values now that they are in 'Table Form'. Currently I am only able to do this by using another CTE below my query that splits out these comma separated values. I'm pretty sure it's possible to just join on the table data without needing another CTE in order to do so.

Code below using CTE's to produce Fake Data. Very reproducible

WITH fake_data AS
(
    SELECT 1 as pkey, 'Billy' as name, 'FE,BF,AF,JF,AA' AS multi_select
)
, lookupTable AS
(
    SELECT 'Forever'   AS lookupValue, 'FE' AS lookupItem UNION ALL
    SELECT 'BoyFriend' AS lookupValue, 'BF' AS lookupItem UNION ALL
    SELECT 'AsFriend' AS lookupValue, 'AF' AS lookupItem
)

, csvToTable AS
(
SELECT value AS lookupItem
FROM fake_data fd
    CROSS APPLY STRING_SPLIT(fd.multi_select, ',')
    /* How do I make the below Left Join Work? */
    --LEFT JOIN lookupTable lt ON lt.lookupItem = fd.value
)

SELECT *
FROM csvToTable csv
--  LEFT JOIN lookupTable lt ON csv.lookupItem = lt.lookupItem

Gif to show Issue

Code Novice · Accepted Answer · 2019-08-23T19:12:54.230

I can't believe it but the answer is so easy I'm almost embarrassed I asked it. Basically I just needed to alias the table being returned by the STRING_SPLIT() function. I just wasn't 'thinking' of the function as a table but since it is returning as a TABLE I can alias it as such and then make use of the fields within it.

The key here was the alias:

CROSS APPLY STRING_SPLIT(fd.multi_select, ',') giving it an alias of csv.

New Code Below showing how to do this:

WITH fake_data AS
(
    SELECT 1 as pkey, 'Billy' as name, 'FE,BF,AF,JF,AA' AS multi_select
)
, lookupTable AS
(
    SELECT 'Forever'   AS lookupValue, 'FE' AS lookupItem UNION ALL
    SELECT 'BoyFriend' AS lookupValue, 'BF' AS lookupItem UNION ALL
    SELECT 'AsFriend' AS lookupValue, 'AF' AS lookupItem
)

SELECT fd.pkey, fd.name, value
FROM fake_data fd
    CROSS APPLY STRING_SPLIT(fd.multi_select, ',') csv
    LEFT JOIN lookupTable lt ON lt.lookupItem = csv.value

Using Cross Apply - How to Join Tables to Cross Apply Join

1 Answers1