Post reply

Split Comma Separated String to Multiple Columns

  • November 1, 2011 at 8:48 pm

    #251050

    Hello Guys

    I am working with SQL Server 2008 and I have to make a T-SQL query to break a comma separated string into multiple columns.

    I have a table like below:

    EMPNO ENAME JOB PHONES_LIST

    1111 SCOTTMANAGER 1111111111,1111111112

    2222 CLARKSALESMAN 2222222221,2222222222

    3333 JAMES CLERK 3333333331,3333333332,3333333333

    And I need the output as below:

    EMPNO ENAME JOB PHONE1 PHONE2 PHONE3

    1111 SCOTTMANAGER 1111111111 1111111112 NULL

    2222 CLARKSALESMAN 2222222221 2222222222 NULL

    3333 JAMES CLERK 3333333331 3333333332 3333333333

    The generation of the number of columns for phone should be handled dynamically. Few times there can be 10 phones or even more in the phones_list. In that case I need 10 columns for Phone just like PHONE1, PHONE2......PHONE10.

    Any help on this is highly appreciated.

    Thanks

    Sunny

  • November 1, 2011 at 10:03 pm

    #1401965

    May I suggest reading this article by Jeff Moden to determine if it can satisfy your requirement.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • November 1, 2011 at 10:38 pm

    #1401968

    To do this, you need a "splitter" function and you need to learn how to do dynamic Cross Tabs. Here are two instructional articles that will help on that. If you want a coded solution, please see the first link in my signature line below...

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

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

  • November 1, 2011 at 10:40 pm

    #1401969

    Heh... Ron beat me to it while I wasn't looking. 😛

    While I'm here, pivoting phone numbers like this is quite contrary to what databases are all about. It might help us make a better recommendation if you told us why you need the phone numbers all on 1 row for any given person. Please don't just say "it's a requirement"... we've got that figured out. What is the business logic reason for the requirement?

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

Viewing 4 posts - 1 through 3 (of 3 total)

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