Post reply

I want to do the opposite of group_concat in SQL Server

  • May 29, 2019 at 11:07 pm

    #3648447

    I want to do the opposite of group_concat in SQL Server.

    My sample data looks like this:

    garry123@gmail.com | A123A,AB263,6543D

    ada121@hotmail.com | 45632A,78YU

    and I want this result like below

    EmailAddress | ProductCode

    ----------------------+---------------

    garry123@gmail.com | A123A

    garry123@gmail.com | AB263

    ada121@hotmail.com | 45632A

    ada121@hotmail.com | 78YU

    garry123@gmail.com | 6543D

    Please advice how to do it?

  • May 29, 2019 at 11:32 pm

    #3648449

    You need Jeff Moden's DelimitedSplit8K function to do that.

    Here's how to use it... (but do read the article - it's really good!)

    CREATE TABLE SomeData(emailaddr VARCHAR(20), Repeating VARCHAR(25));
    GO
    INSERT INTO SomeData VALUES ('garry123@gmail.com', 'A123A,AB263,6543D'),
    ('ada121@hotmail.com','45632A,78YU');

    Then once you have data, you can use it like this:

    Then once you have data, you can use it like this:
    SELECT sd.emailAddr
                  , sd.Repeating
                  , split.Item
                  , split.ItemNumber
    FROM SomeData sd
    CROSS APPLY AdventureWorksDW2016.dbo.DelimitedSplit8k(sd.Repeating,',') split;

    I wasn't clear whether you needed to split the data twice. (Once on the horizontal bar, and then again on the comma), so I only did one split. Otherwise, you'd need to call DelimitedSplit8K on the data twice (so the second time would be on split.Item)

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

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