Lessons Learned #538: Printing One Line Per Field in T-SQL in Azure SQL Database.
Working on a case with a huge number of columns I needed to dump records as one line per field. Happy to share this script that it was useful. For example, I would like to have the data in this way. Id: 123 Status: Open Title: Connectivity issue Running the following TSQL, I was able to obtain the info as I expected. ;WITH src AS ( SELECT top 10000 * FROM dbo.Incidents ORDER BY IncidentId ) SELECT s.IncidentId, f.ord, CONCAT(f.field, ': ', COALESCE(f.value, '')) AS line FROM src AS s CROSS APPLY (VALUES ( 1, N'IncidentId', CONVERT(nvarchar(50), s.IncidentId)), ( 2, N'Status', REPLACE(REPLACE(CONVERT(nvarchar(max), s.Status), CHAR(13), ' '), CHAR(10), ' ')), ( 3, N'Path', REPLACE(REPLACE(CONVERT(nvarchar(max), s.Path), CHAR(13), ' '), CHAR(10), ' ')), ( 4, N'Title', REPLACE(REPLACE(CONVERT(nvarchar(max), s.Title), CHAR(13), ' '), CHAR(10), ' ')), ( 5, N'Severity', REPLACE(REPLACE(CONVERT(nvarchar(max), s.Severity),CHAR(13), ' '), CHAR(10), ' ')), ( 6, N'IsResolved', CONVERT(nvarchar(10), s.IsResolved)), ( 7, N'ResolvedAt', CONVERT(varchar(19), s.ResolvedAt, 126)), -- ISO 8601 ( 8, N'Owner', REPLACE(REPLACE(CONVERT(nvarchar(max), s.Owner), CHAR(13), ' '), CHAR(10), ' ')), ( 9, N'Source', REPLACE(REPLACE(CONVERT(nvarchar(max), s.Source), CHAR(13), ' '), CHAR(10), ' ')), (10, N'Tags', REPLACE(REPLACE(CONVERT(nvarchar(max), s.Tags), CHAR(13), ' '), CHAR(10), ' ')), (11, N'Notes', REPLACE(REPLACE(CONVERT(nvarchar(max), s.Notes), CHAR(13), ' '), CHAR(10), ' ')) ) AS f(ord, field, value) ORDER BY s.IncidentId, f.ord;
