InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 17:48:02 GMT20http://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspx[quote][b]Eliza (10/7/2012)[/b][hr][quote]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. :-)[/quote]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.[/quote]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.Tue, 09 Oct 2012 18:35:18 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspxHeh... 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.Sun, 07 Oct 2012 18:51:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspx[quote][b]frfernan (10/1/2012)[/b][hr]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:[code="sql"]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')GOSELECT * FROM #TUPDATE #TSET Col1 = LEFT(Col1, CHARINDEX(',', Col1) - 1) , Col2 = LTRIM(SUBSTRING(Col1, CHARINDEX(',', Col1) + 1, LEN(Col1)))WHERE CHARINDEX(',', Col1) > 0UPDATE #TSET Col2 = LEFT(Col2, CHARINDEX(',', Col2) - 1) , Col3 = LTRIM(SUBSTRING(Col2, CHARINDEX(',', Col2) + 1, LEN(Col2)))WHERE CHARINDEX(',', Col2) > 0UPDATE #TSET Col3 = LEFT(Col3, CHARINDEX(',', Col3) - 1) , Col4 = LTRIM(SUBSTRING(Col3, CHARINDEX(',', Col3) + 1, LEN(Col3)))WHERE CHARINDEX(',', Col3) > 0SELECT * FROM #TGODROP TABLE #TGO[/code]Hope this helps, Francesc[/quote]Thank you for this I will give it a try.[quote][b]Sergiy (10/1/2012)[/b][hr][quote][b]Eliza (9/17/2012)[/b][hr]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?[/quote]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. :-)[/quote]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.[quote][b]Jeff Moden (10/1/2012)[/b][hr][quote][b]Eliza (9/30/2012)[/b][hr][quote][b]Jeff Moden (9/30/2012)[/b][hr][quote][b]Eliza (9/30/2012)[/b][hr]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.ThanksEliza.[/quote]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?[/quote]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)[/quote]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.[/quote]Thanks Jeff.Thanks allElizaSun, 07 Oct 2012 12:14:07 GMTElizahttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspx[quote][b]Eliza (9/17/2012)[/b][hr]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?[/quote]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. :-)Mon, 01 Oct 2012 18:12:27 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspx[quote][b]Eliza (9/30/2012)[/b][hr][quote][b]Jeff Moden (9/30/2012)[/b][hr][quote][b]Eliza (9/30/2012)[/b][hr]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.ThanksEliza.[/quote]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?[/quote]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)[/quote]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.Mon, 01 Oct 2012 06:32:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspxHello,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:[code="sql"]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')GOSELECT * FROM #TUPDATE #TSET Col1 = LEFT(Col1, CHARINDEX(',', Col1) - 1) , Col2 = LTRIM(SUBSTRING(Col1, CHARINDEX(',', Col1) + 1, LEN(Col1)))WHERE CHARINDEX(',', Col1) > 0UPDATE #TSET Col2 = LEFT(Col2, CHARINDEX(',', Col2) - 1) , Col3 = LTRIM(SUBSTRING(Col2, CHARINDEX(',', Col2) + 1, LEN(Col2)))WHERE CHARINDEX(',', Col2) > 0UPDATE #TSET Col3 = LEFT(Col3, CHARINDEX(',', Col3) - 1) , Col4 = LTRIM(SUBSTRING(Col3, CHARINDEX(',', Col3) + 1, LEN(Col3)))WHERE CHARINDEX(',', Col3) > 0SELECT * FROM #TGODROP TABLE #TGO[/code]Hope this helps, FrancescMon, 01 Oct 2012 05:00:49 GMTfrfernanhttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspx[quote][b]Jeff Moden (9/30/2012)[/b][hr][quote][b]Eliza (9/30/2012)[/b][hr]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.ThanksEliza.[/quote]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?[/quote]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)Sun, 30 Sep 2012 13:35:14 GMTElizahttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspx[quote][b]Eliza (9/30/2012)[/b][hr]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.ThanksEliza.[/quote]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?Sun, 30 Sep 2012 10:24:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspxHi 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.ThanksEliza.Sun, 30 Sep 2012 08:46:47 GMTElizahttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspx[quote][b]Eliza (9/18/2012)[/b][hr][quote][b]Jeff Moden (9/17/2012)[/b][hr][quote][b]Eliza (9/17/2012)[/b][hr]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 have1 - Apple, Pear, Orange 2. - Pear, Apple, BanannaIn my output I would like Col1. Col2. Col31 Apple__Pear____Orange.2 Pear___Apple___BanannaSorry underscores are meant to split the columns.Thank youEliza[/quote]Are the number of elements in each CSV fixed or variable with in the same column?[/quote]The number can vary, however it will be between 0 (null value) and a maximum of 25. It won't go higher than 25.[/quote]Are you all set with this or do you still need some help?Thu, 20 Sep 2012 14:58:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspx[quote][b]Eliza (9/18/2012)[/b][hr][quote][b]Jeff Moden (9/17/2012)[/b][hr][quote][b]Eliza (9/17/2012)[/b][hr]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 have1 - Apple, Pear, Orange 2. - Pear, Apple, BanannaIn my output I would like Col1. Col2. Col31 Apple__Pear____Orange.2 Pear___Apple___BanannaSorry underscores are meant to split the columns.Thank youEliza[/quote]Are the number of elements in each CSV fixed or variable with in the same column?[/quote]The number can vary, however it will be between 0 (null value) and a maximum of 25. It won't go higher than 25.[/quote]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.Tue, 18 Sep 2012 14:17:29 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspx[quote][b]Jeff Moden (9/17/2012)[/b][hr][quote][b]Eliza (9/17/2012)[/b][hr]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 have1 - Apple, Pear, Orange 2. - Pear, Apple, BanannaIn my output I would like Col1. Col2. Col31 Apple__Pear____Orange.2 Pear___Apple___BanannaSorry underscores are meant to split the columns.Thank youEliza[/quote]Are the number of elements in each CSV fixed or variable with in the same column?[/quote]The number can vary, however it will be between 0 (null value) and a maximum of 25. It won't go higher than 25.Tue, 18 Sep 2012 14:04:55 GMTElizahttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspx[quote][b]Eliza (9/17/2012)[/b][hr]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 have1 - Apple, Pear, Orange 2. - Pear, Apple, BanannaIn my output I would like Col1. Col2. Col31 Apple__Pear____Orange.2 Pear___Apple___BanannaSorry underscores are meant to split the columns.Thank youEliza[/quote]Are the number of elements in each CSV fixed or variable with in the same column?Mon, 17 Sep 2012 18:20:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1360495-8-1.aspxHi 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 have1 - Apple, Pear, Orange 2. - Pear, Apple, BanannaIn my output I would like Col1. Col2. Col31 Apple__Pear____Orange.2 Pear___Apple___BanannaSorry underscores are meant to split the columns.Thank youElizaMon, 17 Sep 2012 16:48:50 GMTEliza