concatenate in sql

Question

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

Prasad Raveendran · Answer

try this 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;

Daniel Wright · Answer

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!

concatenate in sql

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)