tsql - Pivoting data on SQL Server

Question

On SQL Server, as part of a more complex query, I'm trying to PIVOT the following data.

FileDataID  FirstName
----------- --------------------
1067513     James
1067513     Arthur
1067513     Kevin
1067513     Walter

The query I am using is

SELECT DISTINCT
    [1] as Name1,
    [2] as Name2,
    [3] as Name3,
    [4] as Name4,
    [5] as Name5
FROM
    (SELECT 
         FirstName as fn
     FROM 
         Borrower
     WHERE
         FileDataID = 1067513) DataTable
PIVOT
    (MAX(DataTable.fn)
     FOR DataTable.fn IN ([1], [2], [3], [4], [5])
    ) AS PivotTable

I am expecting these results.

Name1        Name2        Name3        Name4         Name5  
------------ ------------ ------------ ------------- -------
James        Arthur       Kevin        Walter        NULL

However, the results I get are as follows:

Name1        Name2        Name3        Name4         Name5  
------------ ------------ ------------ ------------- -------
NULL         NULL         NULL         NULL          NULL

I have read far and wide (MS Docs, StackOverflow, etc) but must be missing something. I suspect I have something off with the PIVOT syntax.

Answer 1

You seem to be missing the window function row_number()

Example

Select  [1] as Name1,
        [2] as Name2,
        [3] as Name3,
        [4] as Name4,
        [5] as Name5
 From (
        Select [FileDataID]
              ,[FirstName]
              ,Col = row_number() over (partition by FileDataID order by FirstName)
         From  YourTable
         Where FileDataID = 1067513
      ) src
 Pivot ( max(FirstName) for Col in ([1],[2],[3],[4],[5]) ) pvt

Returns

Name1   Name2   Name3   Name4   Name5
Arthur  James   Kevin   Walter  NULL

EDIT:

Note the order by ... You can use a more specific column like effective date or an identity column if available.