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...

Is an employee distinct in the table? If so, then Why do you need to order by the sum([Annual Salary])? — Taryn
– Taryn, Commented Sep 9, 2014 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
– user3583912, Commented Sep 9, 2014 at 23:04

Community · Accepted Answer · 2017-05-23 10:26:23Z

1

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)

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Sep 9, 2014 at 19:00

Vojtěch Dohnal

8,1123 gold badges53 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

billinkc Over a year ago

Eyeballing it, won't this return 3 rows with one column populated per instead of a single row with 3 columns populated?

Vojtěch Dohnal Over a year ago

@billinkc It returns one row with 3 colums, I can post the whole script if you want to check it yourself.

user3583912 Over a year ago

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

Vojtěch Dohnal Over a year ago

@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.

user3583912 Over a year ago

thanks for the reply. yes I wanted it in PIVOT for some reason. @vasja code working fine. Thanks

vasja · Accepted Answer · 2014-09-09 18:39:11Z

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.

Collectives™ on Stack Overflow

How Can I get Pivot Type Data using T-Sql

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related