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
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
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?

For example if I have

1 - Apple, Pear, Orange
2. - Pear, Apple, Bananna

In my output I would like

Col1. Col2. Col3

1 Apple__Pear____Orange.
2 Pear___Apple___Bananna


Sorry underscores are meant to split the columns.

Thank you
Eliza
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213107 Visits: 41977
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?

For example if I have

1 - Apple, Pear, Orange
2. - Pear, Apple, Bananna

In my output I would like

Col1. Col2. Col3

1 Apple__Pear____Orange.
2 Pear___Apple___Bananna


Sorry underscores are meant to split the columns.

Thank you
Eliza


Are the number of elements in each CSV fixed or variable with in the same column?

--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
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
Jeff Moden (9/17/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?

For example if I have

1 - Apple, Pear, Orange
2. - Pear, Apple, Bananna

In my output I would like

Col1. Col2. Col3

1 Apple__Pear____Orange.
2 Pear___Apple___Bananna


Sorry underscores are meant to split the columns.

Thank you
Eliza


Are the number of elements in each CSV fixed or variable with in the same column?



The number can vary, however it will be between 0 (null value) and a maximum of 25. It won't go higher than 25.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62033 Visits: 17954
Eliza (9/18/2012)
Jeff Moden (9/17/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?

For example if I have

1 - Apple, Pear, Orange
2. - Pear, Apple, Bananna

In my output I would like

Col1. Col2. Col3

1 Apple__Pear____Orange.
2 Pear___Apple___Bananna


Sorry underscores are meant to split the columns.

Thank you
Eliza


Are the number of elements in each CSV fixed or variable with in the same column?



The number can vary, however it will be between 0 (null value) and a maximum of 25. It won't go higher than 25.


Take a look at the link in my signature to the article Jeff wrote for doing just such a thing (splitting a string). Don't just copy and paste the code. Make sure you read it and UNDERSTAND it.

--Edit--
Bit by the spelling bug again.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213107 Visits: 41977
Eliza (9/18/2012)
Jeff Moden (9/17/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?

For example if I have

1 - Apple, Pear, Orange
2. - Pear, Apple, Bananna

In my output I would like

Col1. Col2. Col3

1 Apple__Pear____Orange.
2 Pear___Apple___Bananna


Sorry underscores are meant to split the columns.

Thank you
Eliza


Are the number of elements in each CSV fixed or variable with in the same column?



The number can vary, however it will be between 0 (null value) and a maximum of 25. It won't go higher than 25.


Are you all set with this or do you still need some help?

--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
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
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213107 Visits: 41977
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?

--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
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
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)
gofrancesc
gofrancesc
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1045 Visits: 812
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213107 Visits: 41977
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.

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