SELECT distinct t.NAME, STUFF( (SELECT ',' + s.DATA FROM @tab s WHERE s.NAME = t.NAME FOR XML PATH('')), 1, 1, '') as data
INTO #test
FROM @tab t

DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT 
        @pivot=coalesce(@pivot+',','')+'[DATA'+cast(number+1 as varchar(10))+']'
FROM 
        master..spt_values where type='p' and 
        number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test)

SELECT 
    @select='
        select p.*
        from (
        select 
            name,substring(data, start+2, endPos-Start-2) as token,
            ''DATA''+cast(row_number() over(partition by name order by start) as varchar(10)) as n
        from (
            select 
                name, data, n as start, charindex('','',data,n+2) endPos
                from (select number as n from master..spt_values where type=''p'') num
                cross join 
                (
                    select 
                        name, '','' + data +'','' as data 
                    from 
                        #test
                ) m
            where n < len(data)-1
            and substring(data,n+1,1) = '','') as data
        ) pvt
        Pivot ( max(token)for n in ('+@pivot+'))p'

EXEC(@select)

DROP TABLE #test