9

I have a table-valued function called fn_SplitCommaSep, which comma-separates a text field (from 'a, b, c' to 3 rows: a b c)

How can I join this to a table, taking a table column as an input?

For the purpose of this, say the table MyTable has 2 columns, Id and TextWithCommas, and that table-valued function fn_SplitCommaSep produces one column called TextWithoutComma

eg. something like one of these

select fs.TextWithoutComma
  from fn_SplitCommaSep(select mt.TextWithCommas from MyTable) fs

or 

select fs.TextWithoutComma, mt.Id
  from MyTable mt
    inner join fn_SplitCommaSep(mt.TextWithCommas) fs on (something)
gbn
  • 422,506
  • 82
  • 585
  • 676
mos
  • 157
  • 1
  • 1
  • 8
  • Possible duplicate of [INNER JOIN with Table-Valued Function not working](http://stackoverflow.com/questions/23402316/inner-join-with-table-valued-function-not-working) – Michael Freidgeim Jun 23 '16 at 06:22

1 Answers1

24

Storing comma-separated values in a DB aside, take a look at APPLY

So something like:

SELECT fs.TextWithoutComma, mt.Id 
FROM   MyTable mt 
    CROSS APPLY fn_SplitCommaSep(mt.TextWithCommas) AS fs
Pero P.
  • 25,813
  • 9
  • 61
  • 85