Execute table-valued function on multiple rows?

29,255

OUTER APPLY:

SELECT Stuff.id
    ,Results.pn
    ,Results.s
FROM stackoverflow_454945 AS Stuff
OUTER APPLY dbo.Split(',', Stuff.myColumn) AS Results
WHERE ISNULL(Results.s,'') <> ''
Share:
29,255
Sören Kuklau
Author by

Sören Kuklau

Updated on June 05, 2020

Comments

  • Sören Kuklau
    Sören Kuklau about 4 years

    Given a table-valued function such as dbo.Split() from "T-SQL: Opposite to string concatenation - how to split string into multiple records", how do I pass multiple rows as arguments?

    This works:

    SELECT *
    FROM dbo.Split
      (',', (SELECT myColumn FROM Stuff WHERE id = 22268))
    WHERE ISNULL(s,'') <> ''
    

    It returns:

    pn          s
    ----------- -----------
    1           22351
    2           22354
    3           22356
    4           22357
    5           22360
    

    But this does not:

    SELECT *
    FROM dbo.Split
      (',', (SELECT myColumn FROM Stuff))
    WHERE ISNULL(s,'') <> ''
    

    Nor does this:

    SELECT * FROM dbo.Split_temp(',', myColumn), Stuff
    

    The docs say:

    When a user-defined function that returns a table is invoked in the FROM clause of a subquery, the function arguments cannot reference any columns from the outer query.

    The sort of result set I'm looking for would look something like:

    id          pn          s
    ----------- ----------- -----------
    22268       1           22351
    22268       2           22354
    22268       3           22356
    22268       4           22357
    22268       5           22360
    24104       1           22353
    24104       2           22355
    24104       3           22356
    24104       4           22358
    24104       5           22360
    24104       6           22362
    24104       7           22364
    .
    .
    .
    

    Is there any way at all (aside from, of course, a cursor) to accomplish this?

    (edit)

    As requested by MarlonRibunal, a sample table to produce the above result looks like:

    id          myColumn
    ----------- -------------------------------------------
    22268       22351,22354,22356,22357,22360,
    24104       22353,22355,22356,22358,22360,22362,22364,
    

    id is an int; myColumn is a varchar(max).