SQL Server – Convert Row into Concatenated String with Column Names

pivotsql server

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

Best Answer

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:

enter image description here

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:

enter image description here

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:

enter image description here

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

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

enter image description here

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
Related Question