How can I concatenate a column into a row in SQL?

Concatenating a column into a row in SQL, effectively combining multiple values from a single column into a single row's value, requires using specific techniques which vary slightly across different database systems. This process is commonly referred to as string aggregation or pivoting. Here are some common methods:

1. Using GROUP_CONCAT in MySQL:

In MySQL, you can use the GROUP_CONCAT function to easily concatenate column values. For instance:

SELECT GROUP_CONCAT(column_name SEPARATOR ', ')
FROM table_name;

The SEPARATOR ', ' will insert a comma and a space between each concatenated value, which is optional. If omitted the values are simply put one next to the other. The column_name is the column whose values you want to concatenate, and the table_name represents the database table you are using.

2. Using STRING_AGG in PostgreSQL:

PostgreSQL uses the STRING_AGG function for this purpose, for example:

SELECT STRING_AGG(column_name, ', ')
FROM table_name;

Similar to GROUP_CONCAT , the second argument specifies the separator which will be added between the concatenated string values.

3. Using FOR XML PATH in SQL Server:

SQL Server requires a slightly different syntax utilizing the FOR XML PATH clause. Here’s an example of how to do it :

SELECT STUFF(
(
SELECT ', ' + column_name
FROM table_name
FOR XML PATH (''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, ''
)

FOR XML PATH ('') is used to convert results to XML format and then .value('.', 'NVARCHAR(MAX)') extracts data from that XML in order. The STUFF() function is used to remove the first characters which would appear with that function execution.

4. Using LISTAGG in Oracle:

Oracle has the function LISTAGG. The syntax of this function is:

SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_name)
FROM table_name;

LISTAGG aggregates string values into one, using a chosen separator like ", " in this example. If ORDER BY clause is ommited then order will be unknown.

The method you need to use depends entirely on which SQL Database you are using. Always check the official documentation if you are not sure, but this overview will surely help you concatenate columns in rows!

Learn more about this