code_junkie

Execute table-valued function on multiple rows

Nov 13th, 2011
126
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.33 KB | None | 0 0
  1. SELECT *
  2. FROM dbo.Split
  3. (',', (SELECT myColumn FROM Stuff WHERE id = 22268))
  4. WHERE ISNULL(s,'') <> ''
  5.  
  6. pn s
  7. ----------- -----------
  8. 1 22351
  9. 2 22354
  10. 3 22356
  11. 4 22357
  12. 5 22360
  13.  
  14. SELECT *
  15. FROM dbo.Split
  16. (',', (SELECT myColumn FROM Stuff))
  17. WHERE ISNULL(s,'') <> ''
  18.  
  19. SELECT * FROM dbo.Split_temp(',', myColumn), Stuff
  20.  
  21. id pn s
  22. ----------- ----------- -----------
  23. 22268 1 22351
  24. 22268 2 22354
  25. 22268 3 22356
  26. 22268 4 22357
  27. 22268 5 22360
  28. 24104 1 22353
  29. 24104 2 22355
  30. 24104 3 22356
  31. 24104 4 22358
  32. 24104 5 22360
  33. 24104 6 22362
  34. 24104 7 22364
  35. .
  36. .
  37. .
  38.  
  39. id myColumn
  40. ----------- -------------------------------------------
  41. 22268 22351,22354,22356,22357,22360,
  42. 24104 22353,22355,22356,22358,22360,22362,22364,
  43.  
  44. SELECT Stuff.id
  45. ,Results.pn
  46. ,Results.s
  47. FROM stackoverflow_454945 AS Stuff
  48. OUTER APPLY dbo.Split(',', Stuff.myColumn) AS Results
  49. WHERE ISNULL(Results.s,'') <> ''
  50.  
  51. declare @split varchar(max)
  52. set @split = ''
  53.  
  54. select @split = @split + Coalesce(myColumn + ',' , ' ')
  55. from Stuff
  56. WHERE id = 22268
  57.  
  58. select * from dbo.Split(',', @Left(@split,len(@split) -1))
Add Comment
Please, Sign In to add comment