Concatenate rows into one row in sql with serial number

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

And output for Father Name column

Father Name

(1) Mr. Lucas (2) & (3) Mr. William

How to do it?

Daniel Wright · Answer

To concatenate the Name columns into one column with serial numbers in SQL, you can use the ROW_NUMBER() function along with FOR XML PATH to achieve the desired output. Here is how you can do it: SELECT STUFF((SELECT ' (' + CAST(ROW_NUMBER() OVER (ORDER BY Name) AS VARCHAR) + ') ' + Name FROM Employees FOR XML PATH('')), 1, 1, '') AS Name FROM Employees; In this SQL query: 1. The ROW_NUMBER() function is used to generate serial numbers based on the order of the Name column. 2. The FOR XML PATH('') function is used to concatenate the rows into a single string. 3. The STUFF() function is used to remove the first character (which will be a space) from the concatenated string. This query will give you the concatenated Name column with serial numbers as specified in your desired output. For the Father Name column, you can apply a similar approach: SELECT STUFF((SELECT ' (' + CAST(ROW_NUMBER() OVER (ORDER BY FatherName) AS VARCHAR) + ') ' + FatherName FROM Employees FOR XML PATH('')), 1, 1, '') AS FatherName FROM Employees; You can adjust the column names (Name, FatherName) based on your actual table schema. This SQL query will concatenate the Father Name column with corresponding serial numbers just like you've described. I hope this explanation helps! Let me know if you have any further questions or need more clarification.

Mithila Das · Answer