Recently got a requirement to show the data with serial number from function which splits a delimited string and returns individual items. For instance dbo.Split(‘honda’,’bmw’) returns the honda and bmw as rows. Here the challenge is to show the row number along with the split items in the exact order in which the function returns the data without altering the function definition. Using OVER clause on the function column will give ASC/DESC order rather than what we needed. The below is the code which solves this issue without changing the existing split function.
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))[RowNum], items [Item] FROM dbo.split('Honda,Bmw,Mazda',',')
Below is the result
Hope this helps.