Declare @Table TAble
(
	[Name] varchar(100),
	[Key] int
)

Insert @Table 
Select 'ACS,AMS',2 Union All
Select 'ABC', 3 Union All
Select 'XVY,XYZ', 4


Select [Key],NullIf(SubString(',' + Name + ',' , Number ,   
CharIndex(',' , ',' + Name + ',' , Number) - Number) , '') AS INROW  
FROM 
(
	SELECT Top 100 percent  a.i+b.i as Number FROM 
	(
		Select 0 i union all Select 1 union all  Select 2 union all Select 3 union all Select 4 union all Select 5 union all Select 6 Union All
		Select 7 union all Select 8 union all Select 9 union all Select 10 union all Select 11 union all Select 12 Union All  
		Select 13 union all Select 14 union all Select 15
	) as a,
	(
		Select 0 i union all Select 16  union all Select 32 union all Select 48 union all Select 64 union all Select 80 union all Select 96 Union All
		Select 112 union all Select 128 union all Select 144 union all Select 160 union all Select 176 union all Select 192 Union All  
		Select 208 union all Select 224 union all Select 240
	) as b
	order by 1 
) as F 
Inner Join  @Table
on Number <= Len(',' + Name + ',') AND   
SubString(',' + Name + ',' , Number - 1, 1) = ','   
AND CharIndex(',' , ',' + Name + ',' , Number) - Number > 0  

Order by [key] 

--Output 

Key         INROW                  
----------- ------
2           ACS
2           AMS
3           ABC
4           XVY
4           XYZ

CREATE FUNCTION dbo.fnSplitDelimitedString
(
	@Text VARCHAR(8000),
	@Delimiter VARCHAR(8000)
)
RETURNS @Parts TABLE
	(
		i SMALLINT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
		Part VARCHAR(8000)
	)
AS

BEGIN
	DECLARE	@LastIndex SMALLINT,
		@NextIndex SMALLINT

	IF @Text IS NULL OR DATALENGTH(@Text) = 0
		RETURN

	IF @Delimiter IS NULL
		SELECT	@Delimiter = ';'

	SELECT	@LastIndex = 0,
		@NextIndex = 1

	WHILE @NextIndex > 0
		BEGIN
			SELECT	@NextIndex = CHARINDEX(@Delimiter, @Text, @LastIndex + 1)

			INSERT	@Parts
				(
					Part
				)
			SELECT	CASE
					WHEN @NextIndex = 0 THEN SUBSTRING(@Text, @LastIndex + 1, DATALENGTH(@Text) - @LastIndex)
					ELSE SUBSTRING(@Text, @LastIndex + 1, @NextIndex - @LastIndex - 1)
				END

			SELECT	@LastIndex = @NextIndex
		END

	RETURN
END

-- Prepare test data
DECLARE @Test TABLE ([Name] VARCHAR(1000), [Key] INT)

INSERT	@Test
SELECT	'ACS, AMS', 2 UNION ALL
SELECT	'ABC', 3 UNION ALL
SELECT	'XVY, XYZ', 4

-- Stage the data
DECLARE	@CurrentName VARCHAR(1000),
	@Key INT,
	@MaxName VARCHAR(1000)

SELECT	@CurrentName = MIN([Name]),
	@MaxName = MAX([Name])
FROM	@Test

DECLARE	@Stage TABLE ([Name] VARCHAR(1000), [Key] INT)

WHILE @CurrentName <= @MaxName
	BEGIN
		SELECT	@Key = [Key]
		FROM	@Test
		WHERE	[Name] = @CurrentName

		INSERT	@Stage
		SELECT	LTRIM(RTRIM(Part)),
			@Key
		FROM	dbo.fnSplitDelimitedString(@CurrentName, ',')

		SELECT	@CurrentName = MIN([Name])
		FROM	@Test
		WHERE	[Name] > @CurrentName
	END

-- Show the output
SELECT * FROM @Stage