Convert row into concatenated string with column names included

The following is a bastardized way of doing this, but is useful since it shows how you can use the very powerful "Regular expressions" search-and-replace functionality included in SQL Server Management Studio. Once you get the hang of how this works, it can become something you use almost every day.

"Drag-and-drop" the list of columns from the Object Explorer window onto a Query Window, as shown:

I'm using the dbo.spt_fallback_usg table as an example. After I drag-and-drop them into the query window, the columns are:

xserver_name, xdttm_ins, xdttm_last_ins_upd, xfallback_vstart, dbid, segmap, lstart, sizepg, vstart

Hit [CTRL + H] to open the "Search and Replace" dialog box:

In the example above, I've entered , * into the "Find What" text box, and \n into the "Replace With" text box. Pressing the "Replace All" button will put each column name on a new line. Next, modify the "Search and Replace" items like this:

This will turn each individual column name into a CASE WHEN... statement.

Now replace the "newlines" with +, as in:

This results in the following code in the query window:

CASE WHEN xserver_name IS NULL THEN '' ELSE 'xserver_name: ' + [xserver_name] END + 
CASE WHEN xdttm_ins IS NULL THEN '' ELSE 'xdttm_ins: ' + [xdttm_ins] END + 
CASE WHEN xdttm_last_ins_upd IS NULL THEN '' ELSE 'xdttm_last_ins_upd: ' + [xdttm_last_ins_upd] END + 
CASE WHEN xfallback_vstart IS NULL THEN '' ELSE 'xfallback_vstart: ' + [xfallback_vstart] END + 
CASE WHEN dbid IS NULL THEN '' ELSE 'dbid: ' + [dbid] END + 
CASE WHEN segmap IS NULL THEN '' ELSE 'segmap: ' + [segmap] END + 
CASE WHEN lstart IS NULL THEN '' ELSE 'lstart: ' + [lstart] END + 
CASE WHEN sizepg IS NULL THEN '' ELSE 'sizepg: ' + [sizepg] END + 
CASE WHEN vstart IS NULL THEN '' ELSE 'vstart: ' + [vstart] END + 

This can then be manually wrapped into a SELECT statement like:

SELECT 
    CASE WHEN xserver_name IS NULL THEN '' ELSE 'xserver_name: ' + [xserver_name] END + 
    CASE WHEN xdttm_ins IS NULL THEN '' ELSE 'xdttm_ins: ' + [xdttm_ins] END + 
    CASE WHEN xdttm_last_ins_upd IS NULL THEN '' ELSE 'xdttm_last_ins_upd: ' + [xdttm_last_ins_upd] END + 
    CASE WHEN xfallback_vstart IS NULL THEN '' ELSE 'xfallback_vstart: ' + [xfallback_vstart] END + 
    CASE WHEN dbid IS NULL THEN '' ELSE 'dbid: ' + [dbid] END + 
    CASE WHEN segmap IS NULL THEN '' ELSE 'segmap: ' + [segmap] END + 
    CASE WHEN lstart IS NULL THEN '' ELSE 'lstart: ' + [lstart] END + 
    CASE WHEN sizepg IS NULL THEN '' ELSE 'sizepg: ' + [sizepg] END + 
    CASE WHEN vstart IS NULL THEN '' ELSE 'vstart: ' + [vstart] END 
FROM dbo.spt_fallback_usg 

Depending on how strict your formatting requirements are, you could use the FOR XML syntax. This will generate an XML document for your results, which will give you the field name and value on each row. You could further manipulate the results to de-XML the results back into a more customized format if your example format is a strict requirement.

There are a few different ways to use XML to format your results. Using FOR XML PATH will give you an XML document where each row is wrapped with <FieldName> and </FieldName>. In XML-speak, each field in your table will be it's own node.

SELECT *
FROM dbo.DataDecoded
FOR XML PATH;

Will give XML like:

<row>
  <Id>Value</Id>
  <StandardClientId>Value</StandardClientId>
  <ClientName>Value</ClientName>
  ...etc...
</row>
<row>
  <Id>Value</Id>
  <StandardClientId>Value</StandardClientId>
  <ClientName>Value</ClientName>
  ...etc...
</row>

Using the FOR XML RAW syntax will give one XML node per row, and the different fields are attributes of that node:

SELECT *
FROM dbo.DataDecoded
FOR XML RAW;

Will give XML like:

<row Id="Value" StandardClientId="Value" ClientName="Value" />
<row Id="Value" StandardClientId="Value" ClientName="Value" />

XML & XQuery are pretty powerful, but I'm no expert. I'm sure you could keep going to have a mix of XML & string manipulation to get exactly what you need. Depending on whether this is a one-time thing, or something you need to do often, you could balance how much effort you put into the programmatic solution vs doing some manual cleanup.

You can handle this dynamically using XML, XQuery, and the FLWOR Statement and Iteration syntax:

;WITH blob ([data]) AS
(
      -- *** BEGIN Replace with your query ***
      SELECT so.[object_id], so.[name], so.[schema_id], so.[is_ms_shipped], so.[type],
             IIF(so.[type]='S', NULL, 'NotNull') AS [NullTest]
      FROM   master.sys.objects so
      -- *** END Replace with your query ***
      FOR XML RAW, TYPE 
)
SELECT tab.col.query('
            let $end := local-name((./@*)[last()])
            for $item in ./@*
            return concat(
                    local-name($item),
                    ": ",
                    data($item),
                    if (local-name($item) != $end) then "
" else "")
') AS [ReportValues]
FROM  blob
CROSS APPLY blob.data.nodes('/row') tab(col);

Returns:

object_id: 3
 name: sysrscols
 schema_id: 4
 is_ms_shipped: 1
 type: S 

object_id: 5
 name: sysrowsets
 schema_id: 4
 is_ms_shipped: 1
 type: S 

...

object_id: 1051150790
 name: queue_messages_1035150733
 schema_id: 4
 is_ms_shipped: 1
 type: IT
 NullTest: NotNull

The FOR XML RAW part within the CTE query converts the result set to a single, attribute-based XML document (e.g. <row object_id="3" name="sysrscols" ... /> ). The TYPE keyword causes the result of the FOR XML to be returned as a true XML datatype, else the default is to return NVARCHAR.

The CTE is used to allow that single XML document to be used in the CROSS APPLY so that it can be split back into individual rows via .nodes() (but only for the purpose of matching your existing query behavior, else the end result would still be a single blob).

The FLWOR statement within .query() is used to iterate over whatever attributes (formerly columns) are there.

P.S. The FLWOR syntax used here is a slightly simplified version of something I came up with to answer the following StackOverflow Question related to producing JSON output (not having access to SQL Server 2016):

SQL Server view to provide a consistent table structure using JSON

I mention this because there is logic in the FLWOR syntax to handle putting field delimiters at the end of each line except the final line. Here I changed it to use a newline instead of a comma. However, the original query in the Question takes the easy approach of just placing a newline at the end of each field, including the last field. If that is truly the functionality desired here, then the FLWOR syntax shown above can be reduced further by removing the line starting with let $end... and then changing the if (local-name($item)...) line to be just "
").