Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
table#1

Answered table#1

  • Tuesday, January 29, 2013 8:36 PM
     
      Has Code
    Sign In to Vote
    0

    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 sandra V O Tuesday, February 05, 2013 11:04 PM
    •  
     

All Replies

  • Tuesday, January 29, 2013 9:02 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1

    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
     
     
    Sign In to Vote
    0

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

     
  • Tuesday, January 29, 2013 10:30 PM
    Moderator
     
     
    Sign In to Vote
    1

    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