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

11 comments:

ArdellaJ said...

^^~~輕輕鬆鬆的逛部落格,多謝有您的分享哦~~~........................................

跌倒 said...

旁觀自己的悲傷是解脫,主觀自己的悲傷是更加悲傷........................................

天氣的 said...

上來逛逛,既來之,打聲招呼,留言支持一下囉!祝你一切平安!........................................

微笑每一天 said...

great msg for me, thanks a lot dude˙﹏˙

茂一 said...

做愛自拍照免費線上自拍影片自拍短片國中自拍免費自拍線上免費做愛影片線上免費無碼短片線上免費貼片線上免費漫畫網線上免費影片a線上直播免費a片線上洪爺線上看無碼卡通線上看影片a線上看碼線上美女影片線上做愛小影片線上做愛遊戲線上做愛影片區線上情色看線上情色貼線上情色影片免費線上無碼免費av線上短片免費線上短片免費試看線上看情色影片網線上看情色網線上看做愛小影片免費線上看A線上看A片武俠小說 亂倫小說 嘟嘟成人哈比寬頻成人

明宏明宏 said...

thank for share, it is very important . ̄︿ ̄..................................................

PhilCrispin said...

Hello~Nice to meet you~..................................................

EarleP_Gre2285 said...

IT IS A VERY NICE SUGGESTION, THANK YOU LOTS! ........................................

LonnyAa1105podac said...

視訊援交露點爆乳潮吹裸體裸照裸女愛愛無碼尋夢視訊聊天a漫a片a圖一夜情一葉情人妻激情情色寫真美女自拍辣妹自拍正妹自拍美女走光辣妹走光正妹走光脫衣秀脫衣走光色情自慰自拍成人全裸打炮

雅茹WillyA_Mellinger0724 said...

Many a true word is spoken in jest...................................................

呈婷 said...

謝謝您囉~~很好的經驗分享!........................................