How to convert data separated pipe to rows?



  • I work on sql server 2017 I have table name #countriesData data as below

    my issue here How to convert countries column data separated pipe to rows .

    meaning every rev and company and year will have only one country per row .

       create table #countriesData
     (
     company int,
     [Year] int,
     rev  int,
     countries varchar(500)
     )
     insert into #countriesData(company,[Year],rev,countries)
     values
     (12011,2010,121,'Egypt|France|America'),
     (12011,2011,121,'Egypt|Canda|America'),
     (12011,2012,121,'China|Canda|America'),
     (12099,2010,121,'SaudiArabia|France|America'),
     (12099,2011,121,'Egypt|Canda|German'),
     (12099,2012,121, 'China|Italy|America')
    

    my expected result will be as below : picture for pipe separated rows

    suppose i have two columns have pipe so how to modify query to handle two columns have pipe on same time

     create table #countriesData
     (
     company int,
     [Year] int,
     rev  int,
     countries varchar(500),
     countries1 varchar(500)
     )
     insert into #countriesData(company,[Year],rev,countries,countries2)
     values
     (12011,2010,121,'Egypt|France|America','India|France|America'),
     (12011,2011,121,'Egypt|Canda|America','India|Indonisya|America'),
     (12011,2012,121,'China|Canda|America','Pakistan|Indonisya|America'),
     (12099,2010,121,'SaudiArabia|France|America','Pakistan|sryia|America'),
     (12099,2011,121,'Egypt|Canda|German','Pakistan|Saudia|America'),
     (12099,2012,121, 'China|Italy|America','Holanda|Saudia|America')
    


  • The problem here is that you have violated 1NF by storing multiple values in a single tuple. You are going to have to parse this into rows. You can use the built in https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16 for this.

    select *
    from #countriesData c
    cross apply STRING_SPLIT(c.countries, '|') x
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2