Dec 14, 2009

Split SQL 2005 column values into multiple rows

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

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

kick it on DotNetKicks.com