quote:

---

Originally posted by cat_jesus

check this out.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2#305425



An infinite universe is the ultimate cartesian product.

---

quote:

---

Originally posted by Guestuser18

Hi Guys

Thanks for the replies.
However the first link that has been given is banned at my office!!
The second one isnt very clear as to what I should be looking at. I thouhgt it would be a simple thing to do to split a column data up which has commas in it.

thanks

---

quote:

---

Originally posted by Guestuser18

Do you know of any other links that I could use without me going through the process of unblocking a link?
That would be great.
thanks againm guys.

---

CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns') 
DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT 
        @pivot=coalesce(@pivot+',','')+'[col'+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 
            id,substring(data, start+2, endPos-Start-2) as token,
            ''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
        from (
            select 
                id, 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 
                        id, '','' + 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