sql - Select rows as different columns in SQL Server

Question

I have table with following structure:

I want to show it like this:

Maximum 5 users will be there per LocIC and AuditID

标签： sqlsql-servertsql

Answer 1

你想要条件聚合：

select LocID, AuditID,
       max(case when Seq = 1 then userID end) User1,
       max(case when Seq = 2 then userID end) User2,
       max(case when Seq = 3 then userID end) User3,
       max(case when Seq = 4 then userID end) User4,   
       max(case when Seq = 5 then userID end) User5   
from (select *,
              row_number() over (partition by LocID, AuditID order by userID) Seq
      from table a 
     ) t
group by LocID, AuditID;