Subscribe: Madhivanan : PIVOT, csv
Added By: Feedage Forager Feedage Grade C rated
Language: English
approach work  approach  columns  data  insert test  number  pivot  select select  select  set based  set  start  test  values  varchar 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Madhivanan : PIVOT, csv

Madhivanan : PIVOT, csv

Tags: PIVOT, csv


Splitting delimited data to columns - Set based approach

Thu, 11 Sep 2008 13:06:00 GMT

In this post splitting-csv-to-columns I showed how to split delimited data into multiple columnsSimon in his post Set-based-splitting-of-delimited-strings-to-columns showed how to use that in a set based approach which would work on a set of data.In his approach he has limited it to maximum of four columnsI have modified his approach to work for any number of columns 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 Also Refercsv to multiple columnsDynamic pivot in sql server 2005[...]