Breaking a result into rows and removing comma

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

*/

Open in new window

LVL 8
CamilliaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
like this

?
drop table #result

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'


;with n1 as (select 0 as n union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union select 7 union select 8
union select 9)
,n2 as (select (a.n *10)+b.n as n from n1 as a,n1 as b)
,n3 as (select (a.n*10)+b.n as n from n2 as a,n1 as b)
,n4 as (select (a.n*10)+b.n as n from n3 as a,n1 as b)
,expand as (
select BusinessNameId,BusinessName,','+HCproviders+',' as hcproviders ,n
     ,','+hCids+',' as hcids
  from #result
  cross join n4
  )
,expids as (
select BusinessNameId, BusinessName,n ,hcids
       ,ROW_NUMBER() over (partition by businessnameid order by n) as rn
 from expand
 where SUBSTRING(hcids,n,1)=','
 and n between 1 and len(hcids)
 )
 ,expHC as (
 select BusinessNameId, hcproviders,n ,businessname
       ,ROW_NUMBER() over (partition by businessnameid order by n) as rn
 from expand
 where SUBSTRING(hcproviders,n,1)=','
 and n between 1 and len(hcproviders)
 )
 ,exphc2 as (
 select a.businessnameid,SUBSTRING(a.hcproviders,a.n+1,b.n-a.n-1) as hcproviders
            ,a.rn,a.businessname
  from exphc as a
  inner join expHC as b
   on a.BusinessNameId=b.BusinessNameId
   and a.rn=b.rn-1
   )
   ,expids2 as (
 select a.businessnameid,SUBSTRING(a.hcids,a.n+1,b.n-a.n-1) as hcids
            ,a.rn
  from expids as a
  inner join expids b
   on a.BusinessNameId=b.BusinessNameId
   and a.rn=b.rn-1
   )
  
     select a.BusinessNameId,b.businessname
         ,case when LEN(a.hcids)>0 then a.hcids end as hcids
         ,case when LEN(b.hcproviders)>0 then b.hcproviders end as hcproviders 
     from expids2 as a
     inner join exphc2 as b
     on a.BusinessNameId=b.BusinessNameId
     and a.rn=b.rn

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
ie  

append commas to both ends of the strings
then search each column  to determine the position of the commas
the terms lie between consecutive comma positions

split each string separatly as there terms are of differing variable lengths

rejoin the "2" split strings based on the relative position of the term...

CamilliaAuthor Commented:
let me try. Thanks
CamilliaAuthor Commented:
yes, much better than doing it in C# code. Let me play around with it more to make sure. Thanks again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.