HELP CONVERTING COMMA-DELIMITED COLUMN TO ROWS

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  July 7, 2015 at 11:04 am

  #305551

  CREATE TABLE ANCILLARYVALUES(CUSNO VARCHAR(11), STARTDATE DATE, ENDDATE DATE, VALUELIST VARCHAR(14))

  INSERT INTO ANCILLARY VALUES(CUSNO, STARTDATE, ENDDATE VALUELIST) VALUES('66655544401', 01/01/2015, NULL, 'RX0001,RX0002')

  INSERT INTO ANCILLARY VALUES(CUSNO, STARTDATE, ENDDATE VALUELIST) VALUES('66655544402', 01/01/2015, NULL, 'RX0001')

  INSERT INTO ANCILLARY VALUES(CUSNO, STARTDATE, ENDDATE VALUELIST) VALUES('66655544403', 01/01/2015, NULL, 'RX0001,RX0008')

  I need to transform this data into this:

  CUSNO STARTDATE ENDDATE VALUE

  66655544401 01/01/2015 NULL RX0001

  66655544401 01/01/2015 NULL RX0002

  66655544402 01/01/2015 NULL RX0001

  66655544403 01/01/2015 NULL RX0001

  66655544403 01/01/2015 NULL RX0008

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 7, 2015 at 11:09 am

  #1810768

  Nilssond (7/7/2015)

  ---

  CREATE TABLE ANCILLARYVALUES(CUSNO VARCHAR(11), STARTDATE DATE, ENDDATE DATE, VALUELIST VARCHAR(14))

  INSERT INTO ANCILLARY VALUES(CUSNO, STARTDATE, ENDDATE VALUELIST) VALUES('66655544401', 01/01/2015, NULL, 'RX0001,RX0002')

  INSERT INTO ANCILLARY VALUES(CUSNO, STARTDATE, ENDDATE VALUELIST) VALUES('66655544402', 01/01/2015, NULL, 'RX0001')

  INSERT INTO ANCILLARY VALUES(CUSNO, STARTDATE, ENDDATE VALUELIST) VALUES('66655544403', 01/01/2015, NULL, 'RX0001,RX0008')

  I need to transform this data into this:

  CUSNO STARTDATE ENDDATE VALUE

  66655544401 01/01/2015 NULL RX0001

  66655544401 01/01/2015 NULL RX0002

  66655544402 01/01/2015 NULL RX0001

  66655544403 01/01/2015 NULL RX0001

  66655544403 01/01/2015 NULL RX0008

  This:

  select

  av.CUSNO,

  av.STARTDATE,

  av.ENDDATE,

  ca.Item as VALUE

  from

  ANCILLARYVALUES av

  cross apply (select Item from dbo.DelimitedSplit8K(av.VALUELIST,',')ca(Item);

  Code for the function attached.

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  July 7, 2015 at 11:10 am

  #1810769

  http://www.sqlservercentral.com/articles/Tally+Table/72993/

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service