Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Csv list in column to separate columns Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 4:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 5, 2014 1:33 PM
Points: 33, 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
Post #1360495
Posted Monday, September 17, 2012 6:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1360509
Posted Tuesday, September 18, 2012 2:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 5, 2014 1:33 PM
Points: 33, 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.
Post #1361011
Posted Tuesday, September 18, 2012 2:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's 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)
Post #1361017
Posted Thursday, September 20, 2012 2:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1362332
Posted Sunday, September 30, 2012 8:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 5, 2014 1:33 PM
Points: 33, 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.
Post #1366260
Posted Sunday, September 30, 2012 10:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1366271
Posted Sunday, September 30, 2012 1:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 5, 2014 1:33 PM
Points: 33, 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)
Post #1366291
Posted Monday, October 1, 2012 5:00 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:52 AM
Points: 634, Visits: 809
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
Post #1366482
Posted Monday, October 1, 2012 6:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1366523
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse