troubleshooting Question

Breaking a result into rows and removing comma

Avatar of Camillia
CamilliaFlag for United States of America asked on
Microsoft SQL Server 2005
4 Comments1 Solution231 ViewsLast Modified:
I have a table with results that have comma. I have a sample data below. I need to break the result in indivual rows. I now do it in the code but I want to do it in SQL.

Now sure how to do this. I have a sample table/data below and how I want the result to look like.
Thanks
create table #result 
(
  BusinessNameId int,
  BusinessName varchar(250),
  HCproviders varchar(250),
  hCids varchar(250)

)

insert into #result 
 select 3, 'Test Business33', 'Beh, Bob1 Jones, pp333 ppp, yy fff','18, 1, 11, 16'

insert into #result 
 select 39, 'Username test', 'Linda Hensely','12'

select * from #result

----want it to look like this
/*
BusinessnameId      BusinessName         HCProviders        hcids
3                 Test Business33           Beh              18
3                 Test Business33         Bob1 Jones	      1
3                 Test Business33         pp333 ppp          11
3                 Test Business33         yy fff             16
39                Username test           Linda Hensely      12

*/
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros