Parse Two Delimited Table Columns Into Multiple Records

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  October 22, 2014 at 1:16 pm

  #311961

  I have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.

  CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)

  INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)

  INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '2', 'M231_2)

  INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '3', 'M231_1)

  INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '4', 'M231_2)

  The results I am looking to achieve are:

  PLanID GroupID SubGroupID AddOnCode ExternalCode

  101 000005 LAA 1 M231_1

  101 000005 OCA 2 M231_2

  101 000005 UCA 3 M231_3

  101 000005 XCA 4 M231_4

  201 000005 LAA 1 M231_1

  201 000005 OCA 2 M231_2

  201 000005 UCA 3 M231_3

  201 000005 XCA 4 M231_4

  Is there an SQL statement that can be used to accomplish this?

• Jack Corbett

  SSC Guru

  Points: 184388

  More actions

  October 23, 2014 at 10:26 am

  #1754417

  You should read this article, http://qa.sqlservercentral.com/articles/72993/, by Jeff Moden. As far as I'm concerned that's got the best method for splitting strings.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question