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,'') <> ''
Author by
Sören Kuklau
Updated on June 05, 2020Comments
-
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 anint
;myColumn
is avarchar(max)
.