We help IT Professionals succeed at work.

Breaking a result into rows and removing comma

Camillia
Camillia asked
on
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

Comment
Watch Question

Top Expert 2011
Commented:
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

Top Expert 2011

Commented:
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...

let me try. Thanks
yes, much better than doing it in C# code. Let me play around with it more to make sure. Thanks again.