Separating multiple Comma columns (or other delimaters)

  • Hi all,

    We have data in this format (slightly simplified):

    ID                         DataChanges                            ColumnChanges
    1                           Hi, ABC, 9812, TEST                123,1,44,23
    2                           AS,1244                                     12,133
    3                           BBC, dd                                      12,145

    Here is a code to set up scenario:

    create table #Data

    (ID Int,

    DataChanges varchar(100),

    ColumnChanges varchar(100)

    )

    Insert into #Data

    values (1 ,'Hi, ABC, 9812,TEST','123,1,44,23'),

    (2,'AS,1244','12,133'),

    (3,'BBC,dd','12,145')

    and I want to try and translate it to this:

    ID                         Datachanges                              ColumnChanges
    1                           Hi                                                123
    1                           ABC                                            1
    1                           9812                                          44
    1                          TEST                                           23
    2                           AS                                               12
    2                           1244                                            133
    3                           BBC                                            12
    3                           dd                                               145

    So effectively the first item in Data to be along with the first item in column changes.  I would rather be able to do this without a splitting function (as will need to request access to the server to add this).

    Is it possible without a splitter?

    Even if I did have a splitting function how do I ensure that both columns are split and end up together in the correct order.

    Any help gratefully received.

    Dan

  • danielfountain - Wednesday, April 18, 2018 4:57 AM

    Hi all,

    We have data in this format (slightly simplified):

    ID                         DataChanges                            ColumnChanges
    1                           Hi, ABC, 9812, TEST                123,1,44,23
    2                           AS,1244                                     12,133
    3                           BBC, dd                                      12,145

    Here is a code to set up scenario:

    create table #Data

    (ID Int,

    DataChanges varchar(100),

    ColumnChanges varchar(100)

    )

    Insert into #Data

    values (1 ,'Hi, ABC, 9812,TEST','123,1,44,23'),

    (2,'AS,1244','12,133'),

    (3,'BBC,dd','12,145')

    and I want to try and translate it to this:

    ID                         Datachanges                              ColumnChanges
    1                           Hi                                                123
    1                           ABC                                            1
    1                           9812                                          44
    1                          TEST                                           23
    2                           AS                                               12
    2                           1244                                            133
    3                           BBC                                            12
    3                           dd                                               145

    So effectively the first item in Data to be along with the first item in column changes.  I would rather be able to do this without a splitting function (as will need to request access to the server to add this).

    Is it possible without a splitter?

    Even if I did have a splitting function how do I ensure that both columns are split and end up together in the correct order.

    Any help gratefully received.

    Dan

    I know you said you'd rather not use a splitter, but it really is the right tool for the job.  This query uses Jeff Moden's splitter; it's in my util database and available to all users.


    WITH cteData AS (
    SELECT ID, DataChanges, ColumnChanges
      FROM (VALUES(1, 'Hi, ABC, 9812, TEST', '123,1,44,23'),
          (2, 'AS,1244', '12,133'),
          (3, 'BBC, dd', '12,145')
       ) x (ID, DataChanges, ColumnChanges)
    )
    SELECT d.ID, s1.Item, s2.Item
    FROM cteData d
        CROSS APPLY util.dbo.DelimitedSplit8K(d.DataChanges, ',') s1
        CROSS APPLY util.dbo.DelimitedSplit8K(d.ColumnChanges, ',') s2
    WHERE s1.ItemNumber = s2.ItemNumber
    ORDER BY d.ID, s1.ItemNumber;

    You must have some access to the server to be able to run your query.  If this is typical of the way your systems collect data, I think having a set-based splitter available is a must.  It'll make things so much simpler for more queries than just this one.  I'd suggest making the request to either be able to create it yourself or have it created for you.

    The alternative is to use SQL to recreate the functionality of the splitter and query everything yourself, but it's better to go with proven code and use it whenever you need it.

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

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