May 29, 2019 at 11:07 pm
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
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