Order database view in new columns

Question

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?

Answer 1

This code should get your sorted. Note: the top portion is just to set up sample data.

This will include a row if there is a vendor, but no vendee (VE will be null). It will not include a record if there is a vendee, but no vendor.

-- You Sample Data
WITH YourTableName AS ( 
    SELECT * FROM (
        VALUES ('JAKE' , 'VO'  , 1234)
                ,('JOSH' , 'VE'  , 1234)
                ,('ANNA' , 'VE'  , 1212)
                ,('FAYE' , 'VE'  , 1212)
                ,('MATT' , 'VO'  , 1212)
    ) AS V(NAME, REL, ID)
)
-- Your View Text, replace 'YourTableName' with... wait for it... your table name:
SELECT VO, VE, Vendor.ID, COUNT(*) OVER(PARTITION BY Vendor.ID) AS CNT 
FROM 
    (SELECT Name AS VO, ID FROM YourTableName WHERE Rel = 'VO') Vendor
        LEFT JOIN (SELECT Name AS VE, ID FROM YourTableName WHERE Rel = 'VE') Vendee
        ON Vendor.ID = Vendee.ID
ORDER BY Vendor.ID DESC, VE  -- Can be included, really need to be specified when querying the view