Convert row into concatenated string with column names included

Question

I have a table that is several hundred columns wide. Is there a way to convert each row into a single concatenated string with the column title included without having to list each single column in the query?

I'm doing this because the columns represents fields in an event report. I'm putting them back together so a person can read the report in a logical manner.

I've done some of this with a query, but it is laborious to do for each column and seems error-prone.

Here is a brief snippet showing three columns concatenated in the format I need, done in the column-by-column approach:

SELECT 
 Concat( 
   IIf(Id IS NULL, Null, Concat('Id: ' , [Id] , '\n') )  , 
   IIf(StandardClientId IS NULL, Null, 
     Concat('StandardClientId: ' , [StandardClientId] , '\n') )  , 
   IIf(ClientName IS NULL, Null, 
     Concat('ClientName: ' , [ClientName] , '\n') ) 
 )  AS ReportLine
FROM dbo.DataDecoded; 

Thanks

Answer 1

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 

Answer 2

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.

Answer 3

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 "
").

Answer 4

First a partial answer to your question. You could use dynamic SQL. This would be a starting point:

SELECT ', CONCAT(''' + col.name + ''', ISNULL(['+col.name+'],NULL))'
FROM sys.all_objects obj
JOIN sys.all_columns col
    ON obj.object_id = col.object_id
JOIN sys.schemas sch
    ON obj.schema_id = sch.schema_id
WHERE obj.type IN ('U','S','V')
  AND obj.name = 'databases'
  AND sch.name = 'sys'

The output of this will give you a CONCAT string for each column. You could then dump them into a query and wrap a CONCAT around it (eliminating the first comma). It's possible to do this completely dynamically but even this much is going to give you fits if you have any collation issues. The more dynamic you make something the more careful you have to be to cover every possible variation.

That said .. based on your comment wouldn't it be easier to generate an SSRS report? It would be far more attractive and is what it's actually there for. That's of course assuming you need a report you want to run on a regular basis. If not and if you don't know SSRS, don't have time to learn it, or don't have it installed, another option is to just run your query and save the output as a CSV with headers.

SELECT * FROM sys.databases

Run your query, select all of the output, then right click on the output and select Copy with Headers (or Save Results As .. to save a CSV)

Then open up Excel and paste. You might have to do some formatting for dates & such but that will be a lot less work (and easier for a person to read).

Save your output as a CSV then