www.webdeveloper.com
Results 1 to 9 of 9

Thread: How can I combine two columns into one column using SQL

  1. #1
    Join Date
    May 2005
    Location
    Virginia US
    Posts
    29

    Question How can I combine two columns into one column using SQL

    I am using Microsoft SQL Server 2000.
    I am using JDBC in a java bean to connect to the database and I am using SQL to get data [SELECT] from a particular table.

    With SQL I am trying to combine column A and column B into a single column, say column Z to sort [ORDER BY] by column Z.
    The trick is that if column B is null or empty I want to display column A's data
    otherwise, I want to display column B's data

    Does any one know how to combine two columns into one using SQL?

    This is the only thing that comes to mind

    SELECT ColumnA AS ColumnZ
    FROM Table
    UNION
    SELECT ColumnB AS ColumnZ
    FROM Table
    ORDER BY ColumnZ

    Any hints, ideas, or suggestions are welcome!
    Last edited by gecastill; 06-03-2005 at 09:41 AM.
    Best Regards,
    Gibran E Castillo

  2. #2
    Join Date
    Dec 2002
    Posts
    6
    Here it is tested in MySQL, think it should work in SQL Server.

    SELECT CONCAT(ColumnA, ColumnB) AS ColumnZ
    FROM Table

    ORDER BY 1

  3. #3
    Join Date
    Jan 2004
    Location
    Melbourne, Australia
    Posts
    5,298
    SQL Server does (didn't) not support the CONCAT method. You will need to use syntax such as (expression + expression).
    Code:
    SELECT (ColumnA + ColumnB) AS ColumnZ
    FROM Table;
    Regards.

  4. #4
    Join Date
    Jul 2005
    Posts
    1
    Great finally i know how to combine the column without using any coding.
    By the way do SQL can split the column value? Or this must do in programming code.

  5. #5
    Join Date
    Jul 2006
    Posts
    1

    SUBSTRING command will do it

    This will work in MySQL for sure to get a part of a field, I'm not sure what command powers MS SQL Server. It can be used in the select part of the query or as I'm doing in this example can be used as a comparison.

    This will go from the right, get the last 5 characters in a VIN # to match:
    SELECT i.* FROM schema.inventory AS i LEFT JOIN schema.projects AS p ON SUBSTRING(p.VIN,-5) = i.VIN

    This will go from the left, find the first 3 chars in projects of customers in a specific phone area code... I just made this up right now its untested but should work (you can read up on MySQL developer zone for "SUBSTRING" method and find the exact details):
    SELECT * FROM schema.projects AS p LEFT JOIN schema.customers AS c ON SUBSTRING(c.Phone,3) = p.AreaCode

  6. #6
    Join Date
    Dec 2005
    Location
    India
    Posts
    490
    In sql ..
    select columnA||columnB from TABLEA; (Concats column A and B)
    select substr(columnA,0,2) from TableA; (gets the first two chars Column A of all the records)

  7. #7
    Join Date
    May 2008
    Posts
    1

    Red face Union works, but is there an easier way

    I think I am doing about the same thing... combining two columns into one, creating extra records. Is there an easier way to do it than union?

    this works, but it makes me list all fields twice;
    SELECT user_login AS user_idee, employee FROM employee, grpmebrs
    WHERE USER_ID = user_login
    UNION
    SELECT secgroup AS user_idee, employee FROM employee, grpmebrs
    WHERE USER_ID = user_login

    this concats them;
    SELECT (user_login||secgroup) AS user_idee, employee FROM employee, grpmebrs
    WHERE USER_ID = user_login

    any ideas?
    THANKS!

  8. #8
    Join Date
    Apr 2008
    Location
    Jeddah, Saudi Arabia
    Posts
    43
    Thanks for nice info, I got it here through google...

  9. #9
    Join Date
    Jul 2007
    Posts
    2
    Thanks so much... this is exactly what I needed!

    I have two columns `lastname` and `organization`. If one is filled then the other one is always empty. A normal SELECT * FROM table ORDER BY organization,lastname would list all the organizations first and then the lastnames second, but I wanted to intermix them, so I did this:
    SELECT * FROM table ORDER BY CONCAT(organization,lastname)

    This will combine the two columns for the ORDER BY without actually creating a new column.

Thread Information

Users Browsing this Thread

There are currently 5 users browsing this thread. (0 members and 5 guests)

Posting Permissions

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



Recent Articles