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, October 1, 2012 6:12 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
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.
Post #1366841
Posted Sunday, October 7, 2012 12:14 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
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
Post #1369552
Posted Sunday, October 7, 2012 6:51 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 36,735, Visits: 31,185
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."

(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 #1369586
Posted Tuesday, October 9, 2012 6:35 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
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.
Post #1370642
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse