CODEWITHSUNDEEP
sql-servert-sql
rsjaffe
rsjaffe

Reputation: 5730

Convert row into concatenated string with column names included

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've done some of it with a query, but that 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;

I am using Microsoft SQL Server 2014 Standard.

Thanks

Upvotes: 0

Views: 118

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32220

The easiest way to do this is to write a query that writes the annoying part of your statement for you:

SELECT ORDINAL_POSITION,
    COLUMN_NAME,
    CONCAT('IIF(',COLUMN_NAME,' IS NULL, NULL, CONCAT(''', COLUMN_NAME, ''', '': '',', QUOTENAME(COLUMN_NAME), ', ''\n'')), ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'DataDecoded'
ORDER BY ORDINAL_POSITION;

You can take it a step further and make the whole thing dynamic in an sproc, but that might be a waste of time.

Upvotes: 1

Related Questions