Spliting comma-delimited column to many columns

[xxing]

Hi

I would like to split a column containing comma-delimiter data into many columns.

e.g

TheFruit
Apple,Pear,Kiwifruit,Orange

to

Fruit1 Fruit2 Fruit3 Fruit4
Apple Pear Kiwifruit Orange

This is like a split function, but the results appear in columns not in rows.

Here is the sample data in code

if object_id('tempdb..#Fruit') is not null then drop table #Fruit
create table #Fruit
(
TheFruit varchar(100)
)

insert into #Fruit values ('Apple,Pear,Kiwifruit,Orange')
select * from #Fruit

In my real life data there is 10 items in the TheFruit column, so parsename can't be used, and it would be a nightmare using lots of substrings and charindexs. Does any one know how to do this?

Thank you for your help
Mark

 

[bborissov]

Search FAQ for Split function.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[xxing]

Hi Borislav

Thank you for replying. The split function puts the data in rows, not columns.

E.g Split function puts
Apple,Pear,Kiwifruit,Orange

as
Fruit1
Apple
Pear
Kiwifruit
Orange

I want the data as:
Fruit1 Fruit2 Fruit3 Fruit4
Apple Pear Kiwifruit Orange

i.e the data goes in columns

regards
Mark

 

[xxing]

I found this on the internet courtesy Wayne S at

http://www.sqlservercentral.com/Forums/Topic845680-338-1.aspx

-- if temp table already exists (failed previous run), drop it
if OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test

-- simulate the table with the data in it.
-- NOTE how your sample data was put into a table
-- to make it easier for us volunteers to work with it!
DECLARE @test TABLE (RowData varchar(75))
INSERT INTO @test
SELECT 'xyz 54050, FY11, Apr, Budget, Version_1, 0160117' UNION ALL
SELECT 'abc 54050, FY11, May, Budget, Version_1, 0160117'

-- get the data from the table and put it into a temporary work table
SELECT RowData
INTO #TEST
FROM @test

-- add some columns to hold the comma positions
ALTER TABLE #TEST
ADD Col1EndPos int,
Col2EndPos int,
Col3EndPos int,
Col4EndPos int,
Col5EndPos int

-- need some variables to hold the comma positions for each row
DECLARE @Col1EndPos int,
@Col2EndPos int,
@Col3EndPos int,
@Col4EndPos int,
@Col5EndPos int

-- update the columns to hold the comma positions
UPDATE #Test
SET @Col1EndPos = Col1EndPos = CharIndex(',', RowData),
@Col2EndPos = Col2EndPos = CharIndex(',', RowData, @Col1EndPos + 1),
@Col3EndPos = Col3EndPos = CharIndex(',', RowData, @Col2EndPos + 1),
@Col4EndPos = Col4EndPos = CharIndex(',', RowData, @Col3EndPos + 1),
@Col5EndPos = Col5EndPos = CharIndex(',', RowData, @Col4EndPos + 1)

-- now, get the data for each column
SELECT [ID] = ROW_NUMBER() OVER (ORDER BY RowData),
[Account] = LEFT(RowData, Col1EndPos-1),
[Year] = SUBSTRING(RowData, Col1EndPos+1, Col2EndPos-Col1EndPos-1),
[Month] = SUBSTRING(RowData, Col2EndPos+1, Col3EndPos-Col2EndPos-1),
[Scenario] = SUBSTRING(RowData, Col3EndPos+1, Col4EndPos-Col3EndPos-1),
[Version] = SUBSTRING(RowData, Col4EndPos+1, Col5EndPos-Col4EndPos-1),
[Entity] = SUBSTRING(RowData, Col5EndPos+1, len(RowData)-Col5EndPos-1)
FROM #Test

-- clean up
if OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test
For a thorough description of how to use this form of the update statement, as well as ALL of the

 

[markros]

You may try to first split the values and then PIVOT them.
BTW, there is a very interesting new article by Jeff Moden about string splitting with such a long discussion that I stopped following it.

Check

http://www.sqlservercentral.com/articles/Tally+Table/72993/

PluralSight Learning Library