Help needed in Joins

• amar_kaur16

  SSC Eights!

  Points: 984

  More actions

  September 23, 2014 at 7:24 pm

  #397390

  I have a table that contains student ids that have been assigned to 2 teachers as reader1 and reader2.

  assign table:

  Studentid, User id ReaderName reader#

  123 20 John 1

  123 30 Beth 2

  234 20 John 1

  234 40 Becky 2

  Another table is results table where readers posts the decision as accept or deny for a student if they finish the application.

  Studentid Userid decision

  123 20 accept

  123 30 deny

  234 40 deny

  I want to get only one row as the result like:

  Studentid, reader1, decision1,reader2, decsion2

  123 20 accept 30 deny

  234 40 deny

  if I inner join assign atble with result table on studentid and Userid, can I acheive that?

  Thanks,

  Blyzzard

• mickyT

  SSChampion

  Points: 10360

  More actions

  September 23, 2014 at 8:43 pm

  #1747705

  Basically what you want to do is a Cross Tab query or a Pivot.

  Here's a couple of links

  http://www.sqlservercentral.com/articles/T-SQL/63681/

  http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

  So a crosstab example for your query would be

  SELECT a.StudentID,

  MAX(CASE WHEN Reader# = 1 THEN a.UserID END) Reader1,

  MAX(CASE WHEN Reader# = 1 THEN Decision END) Decision1,

  MAX(CASE WHEN Reader# = 2 THEN a.UserID END) Reader2,

  MAX(CASE WHEN Reader# = 2 THEN Decision END) Decision2

  FROM StudentReaderTable a

  INNER JOIN DecisionTable b ON a.StudentID = b.StudentID and a.UserID = b.UserID

  GROUP BY a.StudentID

  You'll notice that student 123 has the second set of columns populated rather than the first as the decision is for reader# 2