Get data between commas

There are many ways you can do this depending on the version of SQL Server you're using.

SQL Server 2008 -

DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)
SET @str = 'product1;product2'
SET @delimiter = ';'
SET @xml = cast(('<X>'+replace(@str, @delimiter, '</X><X>')+'</X>') as xml);

with data as (
SELECT '1' AS PRODUCT_ID, C.value('.', 'varchar(10)') as PRODUCT_NAME FROM @xml.nodes('X') as X(C)
)
select * from data;

Select allOpen in new window

Here's a reference - http://www.sqlservercentral.com/articles/XML/66932/

In SQL server 2016 there are inbuilt functions for splitting a string so you could try this code -

WITH DATA AS(
	SELECT '1' AS PRODUCT_ID, TRY_CAST(value AS VARCHAR) AS PRODUCT_NAME
	FROM   STRING_SPLIT ('product1;product2', ';') 
)
SELECT * FROM DATA;

Select allOpen in new window

This is a bit difficult to follow. Can you do it from the perspective that product is the column name and there may be more than one product in the column. Also Id is not always 1

It may be like this

ID        Products
1          Prod1, prod2, product3, prodn
2./

Sorry I'd like the outcome to look like this

1 product1
1 product2
2 product3
2 product4

By the way products is a column in a table called events.

So I want to solution to use the table and column name

Hopefully you already have a script for a split function.

You can query it as follows:

select Id, s.data
from events e
outer apply dbo,split(e.Products, ',') s

Select allOpen in new window