34

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).

Community
  • 1
  • 1
Sören Kuklau
  • 19,454
  • 7
  • 52
  • 86

2 Answers2

54

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,'') <> ''
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • Awesome! The second "FROM Stuff" was actually marked as a syntax error, but after removing that, the statement did exactly what I wanted. Thank you. – Sören Kuklau Jan 18 '09 at 13:41
  • Right, I left that in from my copy/paste - I've tested and corrected the code here. – Cade Roux Jan 18 '09 at 13:43
  • Why use `isnull(Results.s,'') <> ''` when `Results.s is not null` also works and is a lot more terse? – tgandrews Jun 25 '10 at 12:53
  • 3
    @tgandrews They aren't equivalent when Results.s = '' - so the isnull(Results.s, '') <> '' also filters out empty string. If that's not a possible output, then it's not necessary. It was in the original question, so I just kept that technique. – Cade Roux Jun 25 '10 at 13:33
  • This is exactly what I was looking for. Trying to include the rows from a table function call using a subquery (SELECT _ FROM ...) was causing me major trouble when there were multiple results. Well done and thank you for the clear answer. – Brien Foss Feb 08 '17 at 00:59
0

You could use the COALESCE concatenation behavior

declare @split varchar(max) 
set @split = ''

select @split = @split + Coalesce(myColumn + ',' , ' ') 
from Stuff 
WHERE id = 22268

select * from dbo.Split(',', @Left(@split,len(@split) -1))

I don't know that that would be any better than using a cursor.

cmsjr
  • 56,771
  • 11
  • 70
  • 62