split comma separated value into new column

Question

Post reply

split comma separated value into new column

latitiacasta

SSCommitted

Points: 1982
More actions
September 29, 2016 at 1:23 am

#313589

Hi,
I have table with column has comma separated value. for e.g. package type as premium,economy. I want to load data into another table which has column like packagetype, packagetype1 . If there is only one value in commaseparated column then i want blank in destination table. Please find attached script to create and insert data into table.
Thanks in advance 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You can use a splitter function like this one: http://www.sqlservercentral.com/articles/Tally+Table/72993/

-- Gianluca Sartori

latitiacasta SSCommitted Points: 1982 More actions · Answer 2

Thanks for your reply. I had gone through it. seems it will give me new row but i want new columns. I am trying to update it as per my requirement.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You can always PIVOT your results after splitting. Would that work for you?

-- Gianluca Sartori

latitiacasta SSCommitted Points: 1982 More actions · Answer 4

pivot will not work as i have multiple columns and 15m records. i use below code but it give me value for first column pcakgetype but it is not working for coveragetype. any suggestion what is wrong here

;WITH CTE AS

(

SELECT

policynumber, policyname,package,

CONVERT(XML,'<Names><name>'

+ REPLACE(packagetype,',', '</name><name>') + '</name></Names>')

AS [packagetype XML],

CONVERT(XML,'<coverages><coverage>'

+ REPLACE(packagetype,',', '</coverage><coverage>') + '</coverage></coverages>')

AS [Coveragetype XML]

FROM commaload

)

SELECT

policynumber, policyname,package,

[packagetype XML].value('/Names[1]/name[1]', 'varchar(50)') As [packagetype],

[packagetype XML].value('/Names[1]/name[2]', 'varchar(50)') As [packagetype1],

[Coveragetype XML].value('/coverages[1]/coverage[1]','varchar(50)') AS Coveragetype,

[Coveragetype XML].value('/coverages[1]/coverage[2]','varchar(50)') AS Coveragetype1,

[Coveragetype XML].value('/coverages[1]/coverage[3]','varchar(50)') AS Coveragetype2

FROM CTE

latitiacasta SSCommitted Points: 1982 More actions · Answer 5

solved i had provided wrong column name. both place i have provided packagetype rather than coveragetype

😛