I've created a view from a table in SQL Server which cannot be altered in any way. The purpose is to make the fairly complex table conceivable for the users.
The view consists of several columns which share an ID and a relation, vendor & vendee, (VO & VE). Think of the ID as some kind of transaction-ID:
NAME | REL | ID JAKE | VO | 1234 JOSH | VE | 1234 ANNA | VE | 1212 FAYE | VE | 1212 MATT | VO | 1212
What I want to do is to create two new columns in the view based on their given relation and sorted by the ID and add a count based on the number of vendees involved in that given transaction.
VO | VE | ID |CNT JAKE | JOSH | 1234 | 1 MATT | ANNA | 1212 | 2 MATT | FAYE | 1212 | 2
Kind of new to SQL, is this even possible to perform with a view?