table#1

# table#1

• Tuesday, January 29, 2013 8:36 PM

Tools: SQL Management Studio 2008

Environment:Windows Vista and SQL Server 2008

Business cases: Job interview takes place in a company. After a panel formed, each employee of this company rate the candidate. The panel is made of 2 - 4 people.

1. four employees/managers interview one candidate and then they score

2. three employees/ managers interview one candidate and then they score

3. two employees/managers interview one candidate and then they score

Code:

```create  Table Mocha ( employee_id int, interview_id int, score_scale NVARCHAR(2))

-- four people interviewed candidate
insert into Mocha values (274679300, 72741, '+2')
insert into Mocha values (352462936, 72741, '+2')
insert into Mocha values (764051400, 72741, '2')
insert into Mocha values (291408848, 72741, '2')

-- three people interviewed candidate
insert into Mocha values (306321924, 75158,'3')
insert into Mocha values (764051400, 75158,'3')
insert into Mocha values (204142329, 75158,'+2')

-- two people interviewed candidate
insert into Mocha values (708743545, 72451,'+2')
insert into Mocha values (72345655, 72451,'+2')

-- two people interviewed candidate
insert into Mocha values (862493644, 74409,'+3')
insert into Mocha values (877460380, 74409,'+3')

Select * from mocha```

I would like to list the following data into

employee 1 | employee 2 | employee 3 | employee 4  | final_rating

+2                     +2               2                       2           |      2

3                      3                +2                      null         |     3

+2                     +2              null                     null         |    +2

+3                    +3                null                     null        |     +3

• Edited by Tuesday, February 05, 2013 11:04 PM
•

### All Replies

• Tuesday, January 29, 2013 9:02 PM
Moderator

Try

```WITH cte
AS (
SELECT *
,row_number() OVER (
PARTITION BY interview_id ORDER BY employee_id
) AS Rn
FROM mocha
)
SELECT interview_id
,max(CASE
WHEN Rn = 1
THEN score_scale
END) AS [Employee 1]
,max(CASE
WHEN Rn = 2
THEN score_scale
END) AS [Employee 2]
,max(CASE
WHEN Rn = 3
THEN score_scale
END) AS [Employee 3]
,max(CASE
WHEN Rn = 4
THEN score_scale
END) AS [Employee 4]
,max(score_scale ) AS [Final Rating] -- max matches your final result - otherwise provide algorithm to determine final rating
FROM cte
GROUP BY interview_id
ORDER BY interview_id```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Tuesday, January 29, 2013 10:02 PM

Thanks for the prompt response. May I know what inspire you to take this approach.

• Tuesday, January 29, 2013 10:30 PM
Moderator

This seems to be a standard problem. This blog post may help

# Understanding SQL Server 2000 Pivot with Aggregates

For every expert, there is an equal and opposite expert. - Becker's Law

My blog