Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
524 views
in Technique[技术] by (71.8m points)

tsql - Pivoting data on SQL Server

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...