Concatenate a Selected Column in a Single Query

Concatenate a selected column in a single query?

Use coalesce. Something like this:

DECLARE @Names varchar(1000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM Employees

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.

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 concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

How to concat all values of single column in mysql

You could use a combination of CONCAT and GROUP_CONCAT

Query

SELECT GROUP_CONCAT(CONCAT('"', A, '"')) AS `combined_A`
FROM `your_table_name`;

And if you want to remove the duplicates. Then use DISTINCT with GROUP_CONCAT.

Query

SELECT GROUP_CONCAT(DISTINCT CONCAT('"', `A`, '"')) AS `combined_A`
FROM `your_table_name`;

SQL Fiddle demo

how to concatenate multiple column values into a single column?

More than one way to achieve this:

SELECT CONCAT(first_name, ' ' ,last_name) AS full_name;

For earlier versions (Where CONCAT is not a built in function):

SELECT first_name + ISNULL(' ' + last_name, '') as Full_Name from [YourTable]

This as well should give you the same result

SELECT COALESCE(first_name, '') + COALESCE(last_name, '') as FullName FROM [YourTable]

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.

---

Related Topics