Geeks With Blogs
Rohit Gupta Engaging talk on Microsoft Technologies ....My Resume

Suppose that you have a column that stored multiple values in a single row using some delimiter as ‘,’.  Now you are tasked with splitting these values into a separate row for each value and you need to do this on multiple rows in the table.

   1: select distinct r.items
   2: from Order o
   3: cross apply dbo.split(o.Products,',') r

SHere the Order Table has a Products column that has multiple Products stored in a single column separated with commas like ‘Tea,Coffee’ associated with a single Order. What the above query does is it gives a distinct list of names of all Products stored in all rows in the Order table. The Split() is a UDF which splits the string into one row for each value

Similarly if you need to check if these multiple values stored in the Products column contain a particular string from a list of values, i,e,

   1: select o.* from Company c
   2: join Order o on c.ItemID = o.companyItemID
   3: where (select * from dbo.split(o.Products) in ('TBD','Tea', 'Coffee'))

Since the above would not compile hence the right solution to do something like this would be:

   1: select o.* from Company c
   2: join Order o on c.ItemID = o.companyItemID
   3: cross apply dbo.Split(Products,',') p 
   4: where p.Items in ('TBD','Tea','Coffee')

what we are doing here is a inner join on the Split() table valued UDF which returns a table containing one row for each value split and then comparing each Item to see if it matches one of the product names i.e. Tea or Coffee.

Posted on Monday, December 14, 2009 9:56 AM | Back to top


Comments on this post: Split SQL 2005 column values into multiple rows

# re: Split SQL 2005 column values into multiple rows
Requesting Gravatar...
Thanx a lot.your function help us lot..
Left by Anupam on May 13, 2010 1:45 AM

# re: Split SQL 2005 column values into multiple rows
Requesting Gravatar...
Its excellent for me.
Left by shahanaj on May 23, 2010 11:47 PM

# re: Split SQL 2005 column values into multiple rows
Requesting Gravatar...
I was searching for smething like cross apply for some time. Hope it works with clr functions. Thanx a lot.
Left by luci79rom on Jul 24, 2011 1:43 AM

Your comment:
 (will show your gravatar)


Copyright © Rohit Gupta | Powered by: GeeksWithBlogs.net