SQL: How can I make this query more flexible

Persons (table Person) passed exams with marks (table Exam) for several disciplines (table Discipline). I have to fetch table [person/discipline] with mark values in cells.

How can I create a more flexible query than this:

SELECT 
  p.*,  -- persons 
  ae.*, -- "A" exam mark 
  be.*, -- "B" exam mark 
  ...

FROM 
  Person p,
  LEFT OUTER JOIN Exam ae ON (p.Id = ae.PersonId and ae.DisciplineId = 1)
  LEFT OUTER JOIN Exam be ON (p.Id = be.PersonId a开发者_运维技巧nd be.DisciplineId = 2)
  ... 

?

I want it to work for an arbitrary range of disciplines.

What do you mean by flexible? You will need to provide some more information on what you want.

At a guess something like the following might give you what you want, although if you provide more information on the table structures we could tell for sure what you want to do.

SELECT 
  p.*,  -- persons 
  (CASE When ae.DisciplineID = 1 THEN ae.Mark ELSE null END) as 'A_Mark',
  (CASE When ae.DisciplineID = 2 THEN ae.Mark ELSE null END) as 'B_Mark',
  ...
FROM 
  Person p,
  LEFT OUTER JOIN Exam ae ON (p.Id = ae.PersonId)
ORDER BY p.Id, ae.DisciplineId

You could group by person, and select the appropriate mark using a case:

SELECT  p.Name
,       min(case when e.DisciplineId = 1 then e.Mark end) as Exam_A_Mark
,       max(case when e.DisciplineId = 2 then e.Mark end) as Exam_B_Mark
FROM    Person p
LEFT JOIN 
        Exam e 
ON      p.Id = e.PersonId
GROUP BY
        p.Id
,       p.Name

Note that the proper SQL approach would be to return one row per exam to the client, and let the client format the result by "pivoting" one row per exam into one column per exam.