Select 2 Columns in One and Combine Them

Select 2 columns in one and combine them

Yes, just like you did:

select something + somethingElse as onlyOneColumn from someTable

If you queried the database, you would have gotten the right answer.

What happens is you ask for an expression. A very simple expression is just a column name, a more complicated expression can have formulas etc in it.

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.

Combining Multiple Columns into One based off another field

Use this :

Select MyId,Col1 as CombininedCol from YourTable where Col1 is not null
Union All
Select MyId,Col2 as CombininedCol from YourTable where Col2 is not null
Union All
Select MyId,Col3 as CombininedCol from YourTable where Col3 is not null
Union All
Select MyId,Col4 as CombininedCol from YourTable where Col4 is not null
order by MyId
 

Tip: The UNION ALL command combines the result set of two or more SELECT statements (allows duplicate values).

Each select separately get the two columns of MyID and the Cole 1,2, etc., and finally returns them as two columns.

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.

SQL SELECT Multiple Columns Into Merge into 1

You can use UNPIVOT but UNION will also work.. Change to UNION ALL to include duplicates..

SELECT  Person1 [Person]
FROM    People
UNION
SELECT  Person2
FROM    People
UNION
SELECT  Person3
FROM    People

if you'd like to try UNPIVOT to compare performance it would look like this.

SELECT DISTINCT
        Person
FROM    People 
UNPIVOT ( 
    Person 
    FOR Persons IN ([Person1],[Person2],[Person3]) 
) up

using a Table Value Constructor

SELECT DISTINCT
        Person
FROM    People
        CROSS APPLY (VALUES(Person1),(Person2),(Person3)) t(Person)

How to combine multiple columns into one column?

SELECT Column1 FROM TableName
UNION ALL
SELECT Column2 FROM TableName
UNION ALL
SELECT Column3 FROM TableName

If you don't want duplicate values, use UNION instead of UNION ALL.

You can also do this using UNPIVOT operator

SELECT Column123
FROM
(
  SELECT Column1, Column2, Column3 
  FROM TableName
) AS tmp
UNPIVOT 
(
  Column123 FOR ColumnAll IN (Column1, Column2, Column3)
) AS unpvt;

https://www.w3schools.com/sql/sql_union.asp

https://www.mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/

SQL Combine Two Columns in Select Statement

I think this is what you are looking for -

select Address1+Address2 as CompleteAddress from YourTable
where Address1+Address2 like '%YourSearchString%'

To prevent a compound word being created when we append address1 with address2, you can use this -

select Address1 + ' ' + Address2 as CompleteAddress from YourTable 
where Address1 + ' ' + Address2 like '%YourSearchString%'

So, '123 Center St' and 'Apt 3B' will not be '123 Center StApt 3B' but will be '123 Center St Apt 3B'.

Combine two columns and add into one new column

Generally, I agree with @kgrittn's advice. Go for it.

But to address your basic question about concat(): it is useful if you need to deal with null values - and null has neither been ruled out in your question nor in the one you refer to.

If you can rule out null values, the good old (SQL standard) concatenation operator || is still the best choice, and @luis' answer is just fine:

SELECT col_a || col_b;

If either of your columns can be null, the result would be null in that case. You could defend with COALESCE:

SELECT COALESCE(col_a, '') || COALESCE(col_b, '');

But that gets tedious quickly with more arguments. That's where concat() comes in, which never returns null, not even if all arguments are null. The manual:

NULL arguments are ignored.

SELECT concat(col_a, col_b);

The remaining corner case for both alternatives is where all input columns are null in which case we still get an empty string ''. To get null instead:

SELECT CASE
          WHEN col_a IS NULL THEN col_b
          WHEN col_b IS NULL THEN col_a
          ELSE col_a || col_b
       END;

This gets more complex with more columns quickly. Again, use concat(), but add a check for the special condition:

SELECT CASE WHEN (col_a, col_b) IS NULL THEN NULL
            ELSE concat(col_a, col_b) END;

How does this work?

(col_a, col_b) is shorthand for ROW (col_a, col_b). And a row type is only null if all columns are null. Detailed explanation:

• NOT NULL constraint over a set of columns

Also, use concat_ws() to add separators between elements (ws for "with separator").

---

An expression like the one in Kevin's answer:

SELECT $1.zipcode || ' - ' || $1.city || ', ' || $1.state;

is tedious to prepare for null values in PostgreSQL 8.3 (without concat()). One way (of many):

SELECT COALESCE(
         CASE
            WHEN $1.zipcode IS NULL THEN $1.city
            WHEN $1.city    IS NULL THEN $1.zipcode
            ELSE $1.zipcode || ' - ' || $1.city
         END, '')
       || COALESCE(', ' || $1.state, '');

Function volatility is only STABLE

concat() and concat_ws() are STABLE functions, not IMMUTABLE because they can invoke datatype output functions (like timestamptz_out) that depend on locale settings.

Explanation by Tom Lane.

This prohibits their direct use in index expressions. If you know that the result is actually immutable in your case, you can work around this with an IMMUTABLE function wrapper. Example here:

• Does PostgreSQL support "accent insensitive" collations?

Select multiple columns from a table, but group by one

I use this trick to group by one column when I have a multiple columns selection:

SELECT MAX(id) AS id,
    Nume,
    MAX(intrare) AS intrare,
    MAX(iesire) AS iesire,
    MAX(intrare-iesire) AS stoc,
    MAX(data) AS data
FROM Produse
GROUP BY Nume
ORDER BY Nume

This works.

---

Related Topics