Post reply

Two columns into one?

  • July 17, 2012 at 9:49 am

    #264003

    Please forgive if this has been asked and answered... I have to move content from one system to another. In the first system, FirstName and LastName are separate columns, but the new system will require this information in Lastname,Firstname format. How can I query the SQL2005 database to return the LastName, followed by a comma and no space, then the FirstName?

  • July 17, 2012 at 9:52 am

    #1513680

    THis?

    select lastname + ' , ' + firstname

    from <YourTable>

  • July 17, 2012 at 9:53 am

    #1513684

    edstewart (7/17/2012)

    Please forgive if this has been asked and answered... I have to move content from one system to another. In the first system, FirstName and LastName are separate columns, but the new system will require this information in Lastname,Firstname format. How can I query the SQL2005 database to return the LastName, followed by a comma and no space, then the FirstName?

    Well I have to say the new system is a horrible design. This is going to cause nothing but pain trying to parse them out later. If at all possible change the schema in the new system to keep these separate.

    Ranting aside...

    select Lastname + ',' + FirstName

    from

    _______________________________________________________________

    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/

  • July 17, 2012 at 9:54 am

    #1513685

    Rough shot, but something like this should work:

    SELECT LastName+CHAR(44)+FirstName

    Adjust the LastName and FirstName as needed to match your column names; the SELECT is there for you to verify the results are correct, so that'll need changing to fit your situation. That be what you're looking for, hopefully 🙂

    - 😀

  • July 17, 2012 at 10:19 am

    #1513703

    Sean Lange (7/17/2012)

    Well I have to say the new system is a horrible design. This is going to cause nothing but pain trying to parse them out later. If at all possible change the schema in the new system to keep these separate.

    Ranting aside...

    +1

    This sort of thing should be sorted in code or at worst a view/computed column.

    It's going to be nearly impossible to search and you'll end up parsing it later.

  • July 17, 2012 at 1:43 pm

    #1513848

    That was too easy. Thank you!

  • July 17, 2012 at 1:44 pm

    #1513850

    Agreed, it is a horrible design. My hands are tied on this one, so I'm have to go with the flow, hence the question. Thanks everyone for their help!

  • July 17, 2012 at 1:48 pm

    #1513854

    edstewart (7/17/2012)

    Agreed, it is a horrible design. My hands are tied on this one, so I'm have to go with the flow, hence the question. Thanks everyone for their help!

    You are welcome.

    Make sure you read the article in my signature about splitting strings because you WILL need this logic in the near future to split this back apart. Then explain to the "powers that be" the errors of their ways for making such a horrid decision.

    _______________________________________________________________

    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/

  • July 17, 2012 at 2:36 pm

    #1513889

    I will. Thank you!

  • July 18, 2012 at 12:37 am

    #1514013

    hisakimatama (7/17/2012)

    Rough shot, but something like this should work:

    SELECT LastName+CHAR(44)+FirstName

    Adjust the LastName and FirstName as needed to match your column names; the SELECT is there for you to verify the results are correct, so that'll need changing to fit your situation. That be what you're looking for, hopefully 🙂

    Why would you obfuscate the code by using CHAR(44) instead of an obvious comma???

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

  • July 18, 2012 at 12:40 am

    #1514014

    Sean Lange (7/17/2012)

    edstewart (7/17/2012)

    Agreed, it is a horrible design. My hands are tied on this one, so I'm have to go with the flow, hence the question. Thanks everyone for their help!

    You are welcome.

    Make sure you read the article in my signature about splitting strings because you WILL need this logic in the near future to split this back apart. Then explain to the "powers that be" the errors of their ways for making such a horrid decision.

    BWAAA-HAAAA!!!! [font="Arial Black"]Just say NO!!![/font] Instead of actually combining the data, make a Computed Column, instead!!!

    --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 11 posts - 1 through 10 (of 10 total)

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