Audit Trail - Report

DECLARE @cols NVARCHAR(MAX),
        @sql  NVARCHAR(MAX);

-- Step 1: Get all unique displaynames for dynamic pivot
SELECT @cols = STRING_AGG(QUOTENAME(displayname), ',')
FROM (
    SELECT DISTINCT displayname
    FROM AuditTrail1
    WHERE AuditTrailNo = '00000009'
) AS d;

-- Step 2: Build dynamic SQL query
SET @sql = '
WITH BaseData AS (
    SELECT 
        A1.AuditTrailNo, 
        AD.docTypeName,
        CONCAT(LEFT(A.yeardocno,4), ''-'', SUBSTRING(A.yeardocno, 5, 2), ''/'', RIGHT(yeardocno, 6)) AS yeardocNo, 
        IIF(Operationcode = ''RC'', ''Re-Created'', 
            IIF(Operationcode = ''MM'', ''Modify Main'', 
                IIF(Operationcode = ''ML'', ''Modify Line'', 
                    IIF(Operationcode = ''DM'', ''Delete Main'', ''Delete Line'')
                )
            )
        ) AS operationname,
        A.operationDate, 
        A.username, 
        A.id,
        A1.displayname,
        ''Old'' AS ValueType,
        A1.oldvalue AS Value
    FROM AuditTrail1 A1
    LEFT JOIN AuditTrail A ON A1.AuditTrailNo = A.AuditTrailNo
    LEFT JOIN AuditTrailDocType AD ON A.DocTypeCode = AD.docType
    WHERE A1.AuditTrailNo = ''00000009''

    UNION ALL

    SELECT 
        A1.AuditTrailNo, 
        AD.docTypeName,
        CONCAT(LEFT(A.yeardocno,4), ''-'', SUBSTRING(A.yeardocno, 5, 2), ''/'', RIGHT(yeardocno, 6)) AS yeardocNo, 
        IIF(Operationcode = ''RC'', ''Re-Created'', 
            IIF(Operationcode = ''MM'', ''Modify Main'', 
                IIF(Operationcode = ''ML'', ''Modify Line'', 
                    IIF(Operationcode = ''DM'', ''Delete Main'', ''Delete Line'')
                )
            )
        ) AS operationname,
        A.operationDate, 
        A.username, 
        A.id,
        A1.displayname,
        ''New'' AS ValueType,
        A1.newvalue AS Value
    FROM AuditTrail1 A1
    LEFT JOIN AuditTrail A ON A1.AuditTrailNo = A.AuditTrailNo
    LEFT JOIN AuditTrailDocType AD ON A.DocTypeCode = AD.docType
    WHERE A1.AuditTrailNo = ''00000009''
)

SELECT 
    AuditTrailNo, 
    docTypeName,
    yeardocNo,
    operationname,
    operationDate,
    username,
    id,
    ValueType,
    ' + @cols + '
FROM BaseData
PIVOT (
    MAX(Value)
    FOR displayname IN (' + @cols + ')
) AS PivotedResult
ORDER BY id DESC, ValueType DESC;
';

-- Step 3: Execute dynamic SQL
EXEC sp_executesql @sql;