Query for combining two SELECTs to get active users and 1 inactive user

Using a CTE will make this query a lot cleaner and probably speed it up a bit as well.

WITH UnionTable AS
(
    SELECT     pk_userid,
               username,
               1 AS active
    FROM       dbo.tbluser AS u
        INNER JOIN tblCustomer AS customer ON u.pk_userid = customer.fk_userid
    WHERE      ( customer.fk_projectid = @projectid
                 AND customer.fk_orderid = @orderid )
    UNION
    SELECT u.pk_userid,
           u.username AS username,
           0        AS active
    FROM   dbo.tbluser AS u
    WHERE  u.pk_userid = @userid
)
SELECT 
        pk_userid
        , username
        , CAST(MAX(active) AS BIT) AS active
FROM UnionTable
GROUP BY pk_userid, username
ORDER BY username

The only thing that concerns me is that you label 2 tables in the first select (nested query) but you don't specify which table you want the pk_userid or username to come from, are they only available in the user table? Even if they only exist in the one table, you should get used to using the aliases you create so that it is clear where the columns are being called from.

You should also make sure that your aliases are meaningful as well. I changed c to customer but I left u as u because user is a reserved word in SQL