How to do advanced PIVOT

Question

I have following table in SQL Server 2016

CREATE TABLE #Source (PatientID INT, DiagnosisCode VARCHAR(20), DX_Pt_RowNum INT)
INSERT INTO #Source
SELECT 1 AS PatientID, 'A' AS DiagnosisCode, 1 AS DX_Pt_RowNum UNION
SELECT 1 AS PatientID, 'B' AS DiagnosisCode, 2 AS DX_Pt_RowNum UNION
SELECT 1 AS PatientID, 'C' AS DiagnosisCode, 3 AS DX_Pt_RowNum UNION
SELECT 2 AS PatientID, 'K' AS DiagnosisCode, 1 AS DX_Pt_RowNum UNION
SELECT 2 AS PatientID, 'T' AS DiagnosisCode, 2 AS DX_Pt_RowNum UNION
SELECT 2 AS PatientID, 'E' AS DiagnosisCode, 1 AS DX_Pt_RowNum 

I know how to do basic PIVOT operation in SQL when we need to aggregate data. But I have a complex scenario as follows. We need to convert the above data in the following PIVOT format. How to achieve this in SQL Server?

Note: I have put 3 columns in this example. In reality, we have 10 columns. Hence JOINing on the same table multiple times may not be ideal.

Answer 1

With the update, a simple PIVOT should do the trick (no need for JOINS)

Select *
 From  (
        Select PatientID
              ,DiagnosisCode
              ,Col = concat('DX',DX_Pt_RowNum )
          From #Source
       ) src
 Pivot (max(DiagnosisCode) for Col in ([DX1],[DX2],[DX3],[DX4],[DX5],[DX6],[DX7],[DX8],[DX9],[DX10] ) ) pvt

Results