Post reply

Extracting split data

  • October 25, 2010 at 6:34 am

    #228243

    Hello all

    I need some help with a problem. I have a table that contains an ID and a column which contains a group of qualifications comma delimited. I am tasked with extracting this data into a new table where the qualification are listed by the entry.Example

    Declare @qualtbl (personID int not null, Qual nvarchar(4000) not null)

    Insert into@qualtbl (personid,qual)

    Select 101,'Qual1,Qual2,Qual3'

    UNION ALL

    Values(102,'Qual1'

    UNION ALL

    Select 103,'Newqual1,Qual1,OldQual2'

    UNION ALL

    Select 104,'Qual1,Qual2,Qual3,FailedQual2'

    The new table needs to be populated as follows

    PersonID QualID Qual

    1011Qual1

    1012Qual2

    1013Qual3

    1021Qual1

    1031Newqual1

    1032Qual1

    1033OldQual2

    1041Qual1

    1041Qual2

    1041Qual3

    1041FailedQual2

    So we need to extract all the persons and split the qual column in the order it is in, I am trying not to use a cursor but at the moment it looks like I am condemmed to that option .

  • October 25, 2010 at 9:23 am

    #1241669

    See this article by Jeff Moden about using the "tally table" approach for splitting delimited strings. First time I used it, it took some time to wrap my head around it, but it also took the run time for a cursor based stored proc I had created from an hour to a minute.

    http://www.sqlservercentral.com/articles/T-SQL/63003/

    On the other hand, if yours is a one-off task with a small number of rows to be processed, I see nothing wrong with using a cursor to just get it done.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply