SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Csv list in column to separate columns


Csv list in column to separate columns

Author
Message
Sergiy
Sergiy
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24951 Visits: 12464
Eliza (9/17/2012)
Hi all,

Can I ask please if I have a row and one column has a csv list in it. Is there a way please to split the csv list into columns in a row?



1. bcp the table to a file.
2. BULK INSERT from the file to a new table using comma as a delimiter.
3. Report success. :-)
Eliza
Eliza
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 81
frfernan (10/1/2012)
Hello,
you can do it in an iterative way:
Find the first colon, keep the left half into Col1 and move the right half into Col2.
Find the first colon in Col2, keep the left half into Col2 and move the right half into Col3.
Find the first colon in Col3, keep the left half into Col3 and move the right half into Col4.
...

You simply should copy and slightly modify an UPDATE statement 25 times.
I coded it in this way:

CREATE TABLE #T (Col1 VARCHAR(200)
, Col2 VARCHAR(200)
, Col3 VARCHAR(200)
, Col4 VARCHAR(200))
INSERT INTO #T (Col1) VALUES ('Apple, Pear, Orange')
INSERT INTO #T (Col1) VALUES ('Pear, Apple, Bananna')
INSERT INTO #T (Col1) VALUES ('Pear, Apple, Bananna, Lemon')
INSERT INTO #T (Col1) VALUES ('Pear, Apple')
INSERT INTO #T (Col1) VALUES ('Kiwi')
GO

SELECT * FROM #T

UPDATE #T
SET Col1 = LEFT(Col1, CHARINDEX(',', Col1) - 1)
, Col2 = LTRIM(SUBSTRING(Col1, CHARINDEX(',', Col1) + 1, LEN(Col1)))
WHERE CHARINDEX(',', Col1) > 0

UPDATE #T
SET Col2 = LEFT(Col2, CHARINDEX(',', Col2) - 1)
, Col3 = LTRIM(SUBSTRING(Col2, CHARINDEX(',', Col2) + 1, LEN(Col2)))
WHERE CHARINDEX(',', Col2) > 0

UPDATE #T
SET Col3 = LEFT(Col3, CHARINDEX(',', Col3) - 1)
, Col4 = LTRIM(SUBSTRING(Col3, CHARINDEX(',', Col3) + 1, LEN(Col3)))
WHERE CHARINDEX(',', Col3) > 0

SELECT * FROM #T
GO

DROP TABLE #T
GO



Hope this helps,
Francesc


Thank you for this I will give it a try.

Sergiy (10/1/2012)
Eliza (9/17/2012)
Hi all,

Can I ask please if I have a row and one column has a csv list in it. Is there a way please to split the csv list into columns in a row?



1. bcp the table to a file.
2. BULK INSERT from the file to a new table using comma as a delimiter.
3. Report success. :-)


This wouldn't work as its a view for a report so I cannot constantly put the data into a file. The view needs to do the chopping for the data but thank you.

Jeff Moden (10/1/2012)
Eliza (9/30/2012)
Jeff Moden (9/30/2012)
Eliza (9/30/2012)
Hi Jeff,

Thanks for your reply. I have have played around with the SQL but still struggling. I can get it to pivot from being rows to columns and used this technique a few times now, but this is going from 1 column to many columns and so if you are able to help any further please do as I'm stuck.

Thanks
Eliza.


Understood. Because ROW_NUMBER() isn't available in SQL Server 2000, this could become a little complex and a fair bit slow depending on the condition of the CSV on each row. So let me ask, is the number of elements within each CSV fixed or does it need to be variable even within the same run?


Hi Jeff,

As mentioned earlier, the number will vary between 0 a null value and a max of 25 elements. Row_Number() is available on our version of SQL server (2008 R2)


Ah! Ok... so we're not limited to SQL Server 2000 which is what I assumed because this is an SQL Server 7/2000 forum. I'll be back.


Thanks Jeff.

Thanks all
Eliza
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)

Group: General Forum Members
Points: 209391 Visits: 41973
Heh... wait a minute now. You want this to all be done in a view? If so, what do you want to be filled in for the other 22 columns if you only have 3 columns to work with?

My recommendation would be to not to try to solve this problem with a view because there's no way to control the number of columns returned in a view.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sergiy
Sergiy
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24951 Visits: 12464
Eliza (10/7/2012)



1. bcp the table to a file.
2. BULK INSERT from the file to a new table using comma as a delimiter.
3. Report success. :-)


This wouldn't work as its a view for a report so I cannot constantly put the data into a file. The view needs to do the chopping for the data but thank you.


Report to be generated as what?
File, I guess?

Then ditch the step 2:

1. bcp the table to a file.
2. Report success. :-)

Whoever reads the report can never tell a column-separating comma from a comma in a list of values.
Its' gonna be the same ASCII character.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search