Combining Records in a query

I have to write a query that searches between 2 rows, but will only match a value in both rows. The data that I am search is a table that holds the first name and last name is separate rows. Don't tell me that I should store them in the same row. I can't and that's all that needs to be said. What I need to do is have a query that combines the records so that I can search for them in separate columns.

The data format is as follows:

StudentValues
    ID
    Stu开发者_StackOverflow社区dentID
    FieldID
    Response

A fieldid of 1 gives me the first name in the response column and fieldid of 2 gives me the last name in the response column and the StudentID would be used to group the records.

I have been able to build the following SQL but the where clauses now fail

    SELECT StudentId, MIN(CASE WHEN FieldId = 1 THEN Response ELSE NULL END) AS fname, MIN(CASE WHEN FieldId = 2 THEN Response ELSE NULL END) AS lname
    FROM sms_studentvalues
    WHERE fname = 'Some Value'
      AND lname = 'Some Value'
    GROUP BY studentid

UPDATED following comment

SELECT *
FROM (
    SELECT  StudentId,
            MIN(CASE WHEN FieldId = 1 THEN Response ELSE NULL END) AS Name, 
            MIN(CASE WHEN FieldId = 2 THEN Response ELSE NULL END) AS LastName
    FROM YourTable
    GROUP BY StudentId) AS StudentsNames
WHERE Name = 'Some Value' AND LastName = 'Some Value'

Have you thought about using a PIVOT on the FieldID? This would allow you to pull both the First and Last name (FieldID=1, FieldID=2) in the same query.

SELECT *
FROM StudentValues s1 JOIN StudentValues s2 ON
  s1.StudentID = s2.StudentID
WHERE
  s1.FieldID = 1 AND s1.Response = 'Larry' AND
  s2.FieldID = 2 AND s2.Response = 'Ellison'

SELECT a.ID, a.StudentID, a.Response, b.Response
FROM StudentValues a
INNER JOIN StudentValues B ON a.ID=b.ID and b.FieldID=2
WHERE a.FieldID=1

A join can do this:

SELECT
    svf.Response AS FirstName,
    svl.Response AS LastName
FROM StudentValues svf
    INNER JOIN StudentValues svl ON svf.StudentID = svl.StudentID
        AND svl.FieldID = 2
WHERE svf.FieldID = 1