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;