-- Arnold Fribble's splitter
CREATE FUNCTION test.AFSplit (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO

IF OBJECT_ID('tempDb..#sample') IS NOT NULL DROP TABLE #sample
CREATE TABLE #sample ([Name] VARCHAR(10), [Value] VARCHAR(8000))
INSERT #sample VALUES
	  ('Brand1', 'A,B,C,D')
	, ('Categ1', 'A,B,C')
	, ('Brand2', 'E,F,G')
	, ('ExtraRowA', 'HI THERE')

DECLARE @sql NVARCHAR(MAX)

SET @sql = N'
SELECT
	[RowInd]'
SELECT @sql = @sql + N'
	, MAX(CASE WHEN [Name] = ''' + [Name] + ''' THEN [splitValue] ELSE NULL END) AS [' + [Name] + ']'
	
FROM
	(
	SELECT DISTINCT [Name] FROM #Sample
	)
	AS s
ORDER BY
	s.[Name]

SET @sql = @sql + '
FROM
	(
	SELECT
		s.[Name] AS [Name]
		, t.[s] AS [SplitValue]
		, ROW_NUMBER() OVER ( PARTITION BY s.[Name] ORDER BY t.[s] ) AS [RowInd]
	FROM
		#sample AS s
		CROSS APPLY test.AFSplit ( '','',  s.[Value] ) AS t
	)
	AS piv
GROUP BY
	piv.[RowInd]'

PRINT @sql
EXEC sp_executeSql @sql

GO

DROP FUNCTION test.AFSplit