Mysql Combine Two Columns into One in Query

MySQL SELECT AS combine two columns into one

If both columns can contain NULL, but you still want to merge them to a single string, the easiest solution is to use CONCAT_WS():

SELECT FirstName AS First_Name
     , LastName AS Last_Name
     , CONCAT_WS('', ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone 
  FROM TABLE1

This way you won't have to check for NULL-ness of each column separately.

Alternatively, if both columns are actually defined as NOT NULL, CONCAT() will be quite enough:

SELECT FirstName AS First_Name
     , LastName AS Last_Name
     , CONCAT(ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone 
  FROM TABLE1

As for COALESCE, it's a bit different beast: given the list of arguments, it returns the first that's not NULL.

MySQL combine two columns into one column

My guess is that you are using MySQL where the + operator does addition, along with silent conversion of the values to numbers. If a value does not start with a digit, then the converted value is 0.

So try this:

select concat(column1, column2)

Two ways to add a space:

select concat(column1, ' ', column2)
select concat_ws(' ', column1, column2)

how to concat two columns into one with the existing column name in mysql?

As aziz-shaikh has pointed out, there is no way to suppress an individual column from the * directive, however you might be able to use the following hack:

SELECT CONCAT(c.FIRSTNAME, ',', c.LASTNAME) AS FIRSTNAME,
       c.*
FROM   `customer` c;

Doing this will cause the second occurrence of the FIRSTNAME column to adopt the alias FIRSTNAME_1 so you should be able to safely address your customised FIRSTNAME column. You need to alias the table because * in any position other than at the start will fail if not aliased.

Hope that helps!

how to combine two columns in mysql and make it as one column?

Try this query :

select a.team_a as team_c from table a Union select b.team_b from table b

Mysql - Merge multiple columns into one while preserving each value

The simplest way is to use union all:

select fk1 from mytable union all
select fk2 from mytable union all
select fk3 from mytable;

There are other methods, but this is simplest.

For your particular query, you can use exists and or or in:

SELECT mo.*
FROM myOtherTable mo
WHERE EXISTS (SELECT 1
              FROM MyTable m
              WHERE mo.id IN (m.fk1, m.fk2, m.fk3)
             );

EDIT:

For performance, but an index on each fk column and use multiple conditions in the where:

SELECT mo.*
FROM myOtherTable mo
WHERE EXISTS (SELECT 1 FROM MyTable m WHERE mo.id = m.fk1) OR
      EXISTS (SELECT 1 FROM MyTable m WHERE mo.id = m.fk2) OR
      EXISTS (SELECT 1 FROM MyTable m WHERE mo.id = m.fk3);

This will do index lookups, which should be much faster than the original form.

vertical merge of two columns into a single one

Try a UNION query:

SELECT nouns, values1 FROM yourTable
UNION ALL
(
    SELECT NULL, values2 FROM yourTable
)

Demo here:

SQLFiddle

Update:

You could also use the nouns column instead of filling with NULL, e.g.

SELECT nouns, values1 FROM yourTable
UNION ALL
(
    SELECT nouns, values2 FROM yourTable
)

combine two columns into one column rows but not same cell and categorize them

Use union all and order by:

select name
from ((select levelname as name, levelid, 1 as ord
       from tablea
      ) union all
      (select '  ' + username, levelid, 2 as ord
       from tableb
      )
     ) ul
order by levelid, ord;

This doesn't actually include the blank rows, which you can also include:

select name
from ((select levelname as name, levelid, 1 as ord
       from tablea
      ) union all
      (select '  ' + username, levelid, 2 as ord
       from tableb
      ) union all
      (select null, levelid, 0 as ord
       from tablea
       where levelid > 1
      )
     ) ul
order by levelid, ord;

All that said. You can do this transformation in SQL. However, it is more typical to do such formatting in the application layer.

---

Related Topics