CREATE TABLE Table1 (Column1 int, Column2 varchar(50))

INSERT INTO Table1 VALUES(1, 'Tara,Mike')
INSERT INTO Table1 VALUES(2, 'Alex,Rebecca,Zach')
GO

CREATE VIEW View1
AS
SELECT 
	Column1,
	PARSENAME(REPLACE(Column2, ',', '.'), 1) as Part1,
	PARSENAME(REPLACE(Column2, ',', '.'), 2) as Part2,
	PARSENAME(REPLACE(Column2, ',', '.'), 3) as Part3,
	PARSENAME(REPLACE(Column2, ',', '.'), 4) as Part4
FROM Table1
GO

SELECT
	Column1,
	Part1
FROM View1
WHERE Part1 IS NOT NULL
UNION ALL
SELECT
	Column1,
	Part2
FROM View1
WHERE Part2 IS NOT NULL
UNION ALL
SELECT
	Column1,
	Part3
FROM View1
WHERE Part3 IS NOT NULL
UNION ALL
SELECT
	Column1,
	Part4
FROM View1
WHERE Part4 IS NOT NULL
ORDER BY 1, 2

DROP VIEW View1
DROP TABLE Table1