CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 12 of 12
  1. #1
    Join Date
    May 2006
    Posts
    12

    Joining 2 fields into 1 Thru SQL

    Is it possible utilizing a SQL statement to take 2 fields, such as firstname and lastname into another field called fullname combining the 2?

    Thank you.

  2. #2
    Join Date
    Apr 2005
    Location
    Norway
    Posts
    3,934

    Re: Joining 2 fields into 1 Thru SQL

    Like this?
    Code:
    SELECT lastname + ', ' + firstname as fullname 
    FROM table
    - petter

  3. #3
    Join Date
    May 2006
    Posts
    12

    Re: Joining 2 fields into 1 Thru SQL

    Really its that easy? I'm going to go modify my SELECT statement.

    Thx

  4. #4
    Join Date
    May 2006
    Posts
    12

    Re: Joining 2 fields into 1 Thru SQL

    Hey that worked great. May I ask another question? Are you not allowed to have spaces when naming columns.

    Your example

    SELECT lastname + ', ' + firstname as fullname
    FROM table

    Works perfect. I tried to give 'fullname' as 'Full Name' but generates an error, obviously because of the space, is there a way around this?

    Thank you

  5. #5
    Join Date
    Apr 2005
    Location
    Norway
    Posts
    3,934

    Re: Joining 2 fields into 1 Thru SQL

    I'm not sure if the is a standard way of doing this (not sure if I even like the idéa), but I belive that you can do this with apostophe (') or brackets ([]) in MS SQL, and back quote (`) in mySQL.

    What database are you targeting?

    - petter

  6. #6
    Join Date
    May 2006
    Posts
    12

    Talking Re: Joining 2 fields into 1 Thru SQL

    Long story short, I've been shortlisted for an entry level position and they have asked to provide 2 solutions as a test prior to the interview. But its for Transact SQL so I will assume Windows SQL Server.

    The naming convention is not a big deal, I was just unsure how to create the one field from two thru SQL. If I couldn't do it on the fly, I was just going to add a field in the table itself and query that.

  7. #7
    Join Date
    Jul 2004
    Posts
    8

    Re: Joining 2 fields into 1 Thru SQL

    Quote Originally Posted by Shasta80
    Is it possible utilizing a SQL statement to take 2 fields, such as firstname and lastname into another field called fullname combining the 2?

    Thank you.

    Is this to DISPLAY the combined 2 fields or do you need to add another field to your table, called fullname, and combine the two fields? IF you need to add a new field then you would need to use the alter table command, and set up this new field. Then do a update on the new field.

  8. #8
    Join Date
    May 2006
    Posts
    2

    Re: Joining 2 fields into 1 Thru SQL

    use

    SELECT lastname + ', ' + firstname as 'full name'
    FROM table

    This works for SQL Server

  9. #9
    Join Date
    May 2006
    Location
    Warsaw, PL/Oslo, NO
    Posts
    19

    Re: Joining 2 fields into 1 Thru SQL

    Quote Originally Posted by Shasta80
    Long story short, I've been shortlisted for an entry level position and they have asked to provide 2 solutions as a test prior to the interview. But its for Transact SQL so I will assume Windows SQL Server.

    The naming convention is not a big deal, I was just unsure how to create the one field from two thru SQL. If I couldn't do it on the fly, I was just going to add a field in the table itself and query that.
    use brackets []. sql server can not misunderstand

    select a as [my new colname] from tab

  10. #10
    Join Date
    Jul 2006
    Posts
    2

    Exclamation Re: Joining 2 fields into 1 Thru SQL

    I understand you can join several fields into 1 thru SQL. Is there a way to join several records with similar ids into a table?

    I have a table that holds projectIDs and Notes for each project. Another table Updates holds projectIDs and individual notes made about the project. I was wondering if there was an easy way to set Notes to the concatination of all individual notes.

  11. #11
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Joining 2 fields into 1 Thru SQL

    You would have to run a query that used one of the JOIN options. You can select records from either one, or both tables. Then, you'd have a recordset that contained all the fields you wanted.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  12. #12
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Re: Joining 2 fields into 1 Thru SQL

    Quote Originally Posted by Imdabaum
    I understand you can join several fields into 1 thru SQL. Is there a way to join several records with similar ids into a table?

    I have a table that holds projectIDs and Notes for each project. Another table Updates holds projectIDs and individual notes made about the project. I was wondering if there was an easy way to set Notes to the concatination of all individual notes.

    No. Turning an N-long result column into a single concatenated string with all the values requires programming logic such as stored procedure or client. If you could guarantee that there would only ever be a max of 10 notes I can give you a wordy sql that will do it, but be aware it requires significant effort, first splitting the results from a column:

    #
    #
    #

    into a diagonal table:
    #__
    _#_
    __#

    into a flat:
    # # #

    into a concatenated:
    ###

    and the number of rows has to be known in advance



    Be careful using "join" to mean "concatenate"
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured