Help needed in Joins

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply