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?