如何在SQL Server中透视两列? [英] How to pivot two columns in SQL Server?

问题描述

我有下表

 UserName   UserId   
    -----      ----    
    Bob         445       
    Bob         450       
    Rachel      512       
    Rachel      520       
    Rachel      570       
    Simon       771       
    Simon       760 

，我正在尝试对其进行调整，以便为每个用户名创建一个新列， 每个用户名列出了用户ID

and I am trying to pivot it so that a new column is created for each username, with UserID's listed per UserName

Bob       Rachel       Simon
445          512        771
450          520        760
             570

推荐答案

以防万一，您正在寻找动态枢纽

Just in case you were looking for a dynamic pivot

示例

Declare @SQL varchar(max) = '
Select *
From (
        Select * 
              ,RN = row_number() over (partition by username order by UserId) 
        from #YourTable
     ) A
 Pivot (max(UserID) For [UserName] in (' + stuff((Select distinct ',' + QuoteName([UserName]) From  #YourTable Order By 1 For XML Path('')),1,1,'')  + ') ) p
'

--Print @SQL
Exec(@SQL);

返回

RN  Bob   Rachel    Simon
1   445   512       760
2   450   520       771
3   NULL  570       NULL

这篇关于如何在SQL Server中透视两列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！