CODEWITHSUNDEEP
sqlsql-serverdatabase
sripriya
sripriya

Reputation: 131

How to separate employee value rows to columns in SQL Server

I'm looking for an efficient way to convert Employee records rows to automatic columns in SQL Server, I heard that PIVOT is not very fast, and I need to deal with lot of records.

I have data records like this below 

EmployeeNumber  Employee Name       Account Number
--------------------------------------------------
100204          Brody, David        4125641146
100204          Brody, David        5300880081

100239          Moland, Luke        34901876
100239          Moland, Luke        0441360299

This is my result: employees have multiple account numbers, I want look like my data 

  EmployeeNumber    Employee Name   AccountNumber  AccountNumber1
 -----------------------------------------------------------------
   100204           Brody, David      4125641146    5300880081
   100239           Moland, Luke      34901876      0441360299

How can I build the result? Can anyone please help me to get my result ?

Upvotes: 0

Views: 83

Answers (1)

Fahmi
Fahmi

Reputation: 37483

You can try below way

select EmployeeNumber,EmployeeName,
max(case when rn=1 then AccountNumber end) as  AccountNumber  ,
max(case when rn=2 then AccountNumber end) as  AccountNumber1
from
(
select EmployeeNumber,  EmployeeName,   AccountNumber,row_number() over(partition by EmployeeName order by AccountNumber) as rn
from tablename
)A group by EmployeeNumber,EmployeeName

Upvotes: 1

Related Questions