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

asked 2016-04-06 by rsjaffe

---

Answer

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 and . 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.

answered 2016-04-06 by Andy Mallon