How to concatenate text from multiple rows into a single text string in SQL server

0 votes

Consider a database table holding names, with three rows:

Niraj

Dey

Nir

Is there an easy way to turn this into a single string of Niraj,Dey,Nir?

Jul 21, 2020 in PHP by kartik
• 37,520 points
1,486 views

1 answer to this question.

0 votes

Hello @kartik,

Use COALESCE:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

Coalesce is really just a helpful cheat that accomplishes two things:

1) No need to initialize @Names with an empty string value.

2) No need to strip off an extra separator at the end.

The solution above will give incorrect results if a row has a NULL Name value (if there is a NULL, the NULL will make @Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

or:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + 
    ISNULL(Name, 'N/A')
FROM People

Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).

Hope it helps!!

Thank You!!

answered Jul 21, 2020 by Niroj
• 82,760 points

Related Questions In PHP

0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

Hello @kartik, In SQL Server, use MERGE MERGE INTO YourTable ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,760 points
179 views
0 votes
1 answer

How do I escape a single quote in SQL Server?

Hello @kartik, Single quotes are escaped by doubling ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,760 points
2,702 views
0 votes
1 answer

How to connect to SQL Server database from JavaScript in the browser?

Hello @kartik, You shouldn´t use client javascript to ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,760 points
7,644 views
0 votes
1 answer

How to put an 'if clause' in an SQL string?

Hello @kartik, For your specific query, you can ...READ MORE

answered Jun 1, 2020 in PHP by Niroj
• 82,760 points
120 views
0 votes
1 answer

jQuery AJAX fires error callback on window unload - how do I filter out unload and only catch real errors?

Hello, In the error callback or $.ajax you have three ...READ MORE

answered Apr 27, 2020 in Java-Script by Niroj
• 82,760 points
934 views
0 votes
1 answer

How do I pass command line arguments to a Node.js program?

Hello @kartik, If your script is called myScript.js ...READ MORE

answered May 5, 2020 in Java-Script by Niroj
• 82,760 points
1,392 views
0 votes
1 answer

Error:Issue when trying to use IN() in wordpress database

Hello @kartik, Try this code : // Create an ...READ MORE

answered May 8, 2020 in PHP by Niroj
• 82,760 points
185 views
+2 votes
1 answer

How do I debug Node.js applications?

Hello @kartik, Use node-inspector  from any browser supporting WebSocket. Breakpoints, ...READ MORE

answered Jul 8, 2020 in Node-js by Niroj
• 82,760 points
195 views
0 votes
1 answer

How to Insert multiple rows in a single SQL query?

Hello @kartik, In SQL Server you can insert ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,760 points
1,954 views
0 votes
1 answer

How to insert multiple rows from a single query using eloquent/fluent?

Hello @kartik, You can use the following approach. $data ...READ MORE

answered Aug 14, 2020 in PHP by Niroj
• 82,760 points
241 views