September 23, 2014 at 7:24 pm
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
September 23, 2014 at 8:43 pm
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