How Can I get Pivot Type Data using T-Sql

Question

I got requirement that needs to display top 3 salary Employee. It got query which displaying top 3 employee names like this:

select top 3 [Employee First Name]+' '+[Employee Surname] as [Employee Full Name]  
from [Emp ]
group by [Employee First Name],[Employee Surname],[Annual Salary]
order by sum([Annual Salary]) desc

Result:

[EmpFullName]
---------------
Darren Ben
Sam nixon
Frances Oliv

But I want result like:

[FirstEmp]    [SecondEmp]   [ThirdEmp]
-------------------------------------------
Darren Ben    Sam nixon     Frances Oliv

I want hard-coded columns and names under it.

Thanks for any advise...

check out [transpose](http://stackoverflow.com/questions/20057655/multiple-rows-in-one-column-sql-server) — Tak, Sep 09 '14 at 17:25
Is an employee distinct in the table? If so, then Why do you need to order by the `sum([Annual Salary])`? — Taryn, Sep 09 '14 at 19:16
@ bluefeet, thanks for the reply. Yes your right no need of sum([Annual Salary]). I just altered my query, it just 'Order by [Annual Salary] desc'. — user3583912, Sep 09 '14 at 23:04

score 1 · Answer 1 · edited May 23 '17 at 10:26

I found this solution, working, but not necessarily the best one. It is based on adding row_number column to your query and then selecting your 3 columns using COALESCE(MAX(. (see SQL Server : Transpose rows to columns) It seems to me better in this case than using t-sql PIVOT.

SELECT 
COALESCE(MAX(CASE WHEN x = 1 THEN [Employee Full Name] END), 'n/a') as FirstEmp,
COALESCE(MAX(CASE WHEN x = 2 THEN [Employee Full Name] END), 'n/a') as SecondEmp,
COALESCE(MAX(CASE WHEN x = 3 THEN [Employee Full Name] END), 'n/a') as ThirdEmp
FROM (
    select top 3 
    row_number() over(order by [Annual Salary] desc) x,
    [Employee First Name]+' '+[Employee Surname] as [Employee Full Name]
    from Emp
    group by [Employee First Name],[Employee Surname],[Annual Salary]
    order by [Annual Salary] desc
    ) empRanks

I have replaced order by sum([Annual Salary]) desc with order by [Annual Salary] desc because the inner SELECT is grouped by [Annual Salary] already.

Script I have used to populate data to test it:

DECLARE @emp TABLE
(
  [Employee First Name] varchar(50) NULL,
  [Employee Surname] varchar(50) NULL,
  [Annual Salary] int NULL
)

INSERT INTO @emp VALUES ('Darren','Ben',500)
INSERT INTO @emp VALUES ('Sam','nixon',600)
INSERT INTO @emp VALUES ('Frances','Oliv',700)
INSERT INTO @emp VALUES ('AAFrances','AAAOliv',200)
INSERT INTO @emp VALUES ('AAFrancsasaes','AAAOsasaliv',2000)

Eyeballing it, won't this return 3 rows with one column populated per instead of a single row with 3 columns populated? — billinkc, Sep 09 '14 at 19:11
@billinkc It returns one row with 3 colums, I can post the whole script if you want to check it yourself. — Vojtěch Dohnal, Sep 09 '14 at 19:13
VDohnal, thanks for the reply. I got answer with your code, really thanks for that. Can you please tell me can i get same answer from Pivot table — user3583912, Sep 10 '14 at 00:06
@user3583912 Do you mean using t-sql `PIVOT` relational operator? See @vasja answer, I do not see anything so wrong with it, I believe it got -1 just because it was code-only answer. — Vojtěch Dohnal, Sep 10 '14 at 04:47
thanks for the reply. yes I wanted it in PIVOT for some reason. @vasja code working fine. Thanks — user3583912, Sep 10 '14 at 08:37

score 0 · Accepted Answer · answered Sep 09 '14 at 18:39

Maybe someone can simplify this:

create table #Emp (EmpId integer identity, [Employee First Name] varchar(30), [Employee Surname]  varchar(30), [Annual Salary] decimal)

insert into #Emp ([Employee First Name], [Employee Surname], [Annual Salary] )
values 
('Darren', 'Ben', 100000),
('Sam', 'Nixon', 80000),
('Frances', 'Oliv', 70000)


select * from (
select 
case (_order)
when 1 then 'FirstEmp'
when 2 then 'SecondEmp'
when 3 then 'ThirdEmp'
end as col
, q.name
from (
    select top 3 ROW_NUMBER() OVER ( ORDER BY [Annual Salary]  desc) as _order, [Employee First Name]+' '+[Employee Surname] as name
    from [#Emp ]
    group by EmpId, [Employee First Name],[Employee Surname],[Annual Salary]
    order by 1
) as q
) as o
pivot 
(
min(name)
for col in (FirstEmp, SecondEmp, ThirdEmp)
) as p

Uses ROW_NUMBER() OVER ... to calculate order, then PIVOT to transpose the rows to columns.

How Can I get Pivot Type Data using T-Sql

2 Answers2