sql-server - 具有非整数值且没有聚合函数的数据透视表

Question

我需要根据行中名称的职业获得结果。下面是示例数据的结构。

create table Employees (name varchar(10), occupation varchar(12))

Insert into Employees values ('Member1','Doctor')
Insert into Employees values ('Member2','Doctor')
Insert into Employees values ('Member3','Lawyer')
Insert into Employees values ('Member4','Teacher')
Insert into Employees values ('Member5','Lawyer')
Insert into Employees values ('Member6','Teacher')
Insert into Employees values ('Member7','Lawyer')

根据我的理解，它可以通过 sql server 中的 PIVOT 实现，但它需要一些聚合函数，我可以使用 MAX 这将给出 MAX(value1)=value1 我在下面的查询中尝试过：

Select * from 
(Select name,occupation from Employees) as emp
PIVOT 
(
  MAX(name) for occupation in (Doctor,Teacher,Lawyer)
) as pvt

它给了我结果

Doctor  Teacher Lawyer
Member2 Member6 Member7

但是我需要在下面的结构中得到结果，因为列是按升序排列的

Doctor  Teacher Lawyer
Member1 Member4 Member3
Member2 Member6 Member5
NULL    NULL    Member7

标签： sql-server

Answer 1

再会，

请检查此解决方案：

Select * 
from (
    Select 
        [name],[occupation], 
        RN = ROW_NUMBER() OVER (partition by [occupation] order by [name],[occupation])
    from Employees
) t
PIVOT (
    MAX(name) for occupation in (Doctor,Teacher,Lawyer)
) as pvt