concatenate in sql

I have a data table Employee like below. I want to Concatenate Name columns into one columns with serial number

Name	Father Name
Mr. James	Mr. Lucas
Mr. Mateo	Mr. William
Mr. Noah	Mr. William

I want following output for name column

Name

(1) Mr. James (2) Mr. Mateo (3) Mr. Noah

if data table has single row then like below

Name	Father Name
Mr. James	Mr. Lucas

then output is without serial number

Name

Mr. James

WITH EmployeeData AS ( SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) AS RowNum FROM Employee ) SELECT CASE WHEN COUNT(*) = 1 THEN MAX(Name) ELSE STRING_AGG(CONCAT('(', RowNum, ') ', Name), ' ') END AS Name FROM EmployeeData;

Concatenating columns in SQL, especially with additional formatting like serial numbers, can be achieved using the `CONCAT` function along with other SQL functions. Let's look at how you can concatenate the "Name" column with serial numbers as requested.

Since the output needs to include serial numbers for multiple rows, you can use the `ROW_NUMBER()` function to generate serial numbers. Here's how you can write the SQL query to achieve the desired output:


WITH NumberedNames AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY Name) AS SerialNumber,
        Name
    FROM 
        Employee
)
SELECT 
    CONCAT('(', SerialNumber, ') ', Name) AS Name
FROM 
    NumberedNames;

In the above SQL query:

- We first use a Common Table Expression (CTE) `NumberedNames` to generate serial numbers for each row using `ROW_NUMBER()`.

- Then, we concatenate the serial number with the name using the `CONCAT` function in the final select statement.

This query will produce the concatenated output with serial numbers for each name in the "Name" column as shown in the desired output table.

In the case where there is only a single row in the table, you can simply select and concatenate the name without serial numbers like this:


SELECT 
    Name
FROM 
    Employee;

This query will output the name without any serial number as indicated in the second sample table.

Feel free to try out these queries with your Employee table data to see the concatenated output. If you have any more questions or need further clarification, feel free to ask!

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Prasad Raveendran1y ago

try this

Daniel Wright1y ago
Concatenating columns in SQL, especially with additional formatting like serial numbers, can be achieved using the `CONCAT` function along with other SQL functions. Let's look at how you can concatenate the "Name" column with serial numbers as requested.

Since the output needs to include serial numbers for multiple rows, you can use the `ROW_NUMBER()` function to generate serial numbers. Here's how you can write the SQL query to achieve the desired output:
```
WITH NumberedNames AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY Name) AS SerialNumber,
        Name
    FROM 
        Employee
)
SELECT 
    CONCAT('(', SerialNumber, ') ', Name) AS Name
FROM 
    NumberedNames;
```
In the above SQL query:

- We first use a Common Table Expression (CTE) `NumberedNames` to generate serial numbers for each row using `ROW_NUMBER()`.

- Then, we concatenate the serial number with the name using the `CONCAT` function in the final select statement.

This query will produce the concatenated output with serial numbers for each name in the "Name" column as shown in the desired output table.

In the case where there is only a single row in the table, you can simply select and concatenate the name without serial numbers like this:
```
SELECT 
    Name
FROM 
    Employee;
```
This query will output the name without any serial number as indicated in the second sample table.

Feel free to try out these queries with your Employee table data to see the concatenated output. If you have any more questions or need further clarification, feel free to ask!