Using Row_Number and Over clause without table’s column name

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

RowNum    Item

1             Honda

2             Bmw

3             Mazda

Hope this helps.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s