Post reply

Csv list in column to separate columns

  • September 17, 2012 at 4:48 pm

    #267870

    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

  • September 17, 2012 at 6:20 pm

    #1538860

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 18, 2012 at 2:04 pm

    #1539287

    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.

  • September 18, 2012 at 2:17 pm

    #1539292

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • September 20, 2012 at 2:58 pm

    #1540396

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 30, 2012 at 8:46 am

    #1543739

    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.

  • September 30, 2012 at 10:24 am

    #1543748

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 30, 2012 at 1:35 pm

    #1543763

    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)

  • October 1, 2012 at 5:00 am

    #1543914

    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

  • October 1, 2012 at 6:32 am

    #1543950

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 1, 2012 at 6:12 pm

    #1544205

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

    _____________
    Code for TallyGenerator

  • October 7, 2012 at 12:14 pm

    #1546507

    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

  • October 7, 2012 at 6:51 pm

    #1546534

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 9, 2012 at 6:35 pm

    #1547425

    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.

    _____________
    Code for TallyGenerator

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply