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

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. 🙂

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.