Occasionally I have come across the need to save the output from a query to a varchar value, for instance varchar(max). There are lots of ways to do this, could you use a cursor and just append to a string each loop through the cursor, but there is an easier way.

Examples of a need to do this would be to write the output to a file, or to build an email message that is going to be sent, or many other possible scenarios.

Lets start with a simple query on SQL Server 2019, grabbing a list of databases, and with a where clause to keep the list short for now.

--start with a simple query
SELECT * 
  FROM sys.databases
  WHERE database_id < 9;

Which produces output something like this.

Now lets trim it down to just the data we want to build into string output.