I am trying to build a query that will drive a view. The functionality i am looking for seems to come up quite often and i'm pretty sure their is a better alternative to it than using a subquery with a CASE like i have been doing.

Lets say we have two tables with ids with primary keys like so. Lets say for this example that table b has a constraint where only one teacher can be assigned to each grade, but any number of teachers can be assigned to a grade.

```html
table a

gradeid    grade  
-------------------
1           A      
2           B     
3           C     
4           D     
5           F  

table b

teachid  gradeid  teacher
-------------------------
1      1      mary    
2       1      bob 
3      2      sue  
4        3      sally  
5        4      sally  
6        5      rich 
7        1      bill
8        2      bill 
9        3      chris 
```

Now lets say we want to write a view query that is for some export to excel functionality that takes data from Table a and joins it on table b in a way where we can see if there is a positive join for a particular grade without returning multiple rows per match and having to manually flatten the data. What we want is a view that is locked by the number of results from table A (in this example 5, but we could add more grades and have it expand down) Here is what our view would look like:

*Note* Rest signifies all other unique teacher matches that are not bob or billaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

```html
View

gradeid  grade  bobsname bobsid billsname billsid restids restnames
-------------------------------------------------------------------
1      A      bob 2     bill   7   1   mary
2       B      null  null bill   8   3   sue
3      C      null   null null   null    4,9     sally,chris
4        D      null   null null   null    5       sally
5        F      null   null null   null    6       rich
```

The way i could do this would be to use subqueries with a CASE statement.

Something like 

```html
SELECT a.gradeid, a.grade, 
CASE WHEN (select bob.teachid from tableb bob left join on a.gradeid = bob.gradeid) IS NOT NULL (select bob.teachid from tableb bob left join on a.gradeid = bob.gradeid) ELSE NULL END as bobsid,
CASE WHEN (select bob.teacher from tableb bob left join on a.gradeid = bob.gradeid) IS NOT NULL (select bob.teacher from tableb bob left join on a.gradeid = bob.gradeid) ELSE NULL END as bobsname,
CASE WHEN (select bill.teachid from tableb bill left join on a.gradeid = bill.gradeid) IS NOT NULL (select bill.teachid from tableb bill left join on a.gradeid = bill.gradeid) ELSE NULL END as billsid,
CASE WHEN (select bill.teacher from tableb bill left join on a.gradeid = bill.gradeid) IS NOT NULL (select bill.teacher from tableb bill left join on a.gradeid = bill.gradeid) ELSE NULL END as billsname,
CASE WHEN REALLY LONG THING THAT IS TOO UGLY TO WRITE AND MAKES ME ASK HERE ETC....
FROM table a
```

This may be the only way to accomplish this, but i believe that there has to be a better way to do this. Any suggestions?