Using Two Columns In A Pivot

I have posted this question Use many to many relation to generate columns like a pivot and got the answer, but now I need one more thing in the resultset. My User table has a Regis

Solution 1:

I would consider using the conditional aggregation approach instead of using the pivot operator.

I think this query should do what you want:

SELECT  
    ProjectID              = P.Id, 
    ProjectName            = P.Name, 
    [UserType0 (Name)]     =MAX(CASEWHEN MemberType =0THEN u.Name END),
    [UserType0 (Register)] =MAX(CASEWHEN MemberType =0THEN Register END), 
    [UserType1 (Name)]     =MAX(CASEWHEN MemberType =1THEN u.Name END) 
FROM Project AS P
LEFTJOIN ProjectMember AS PM ON P.Id = PM.Project_Id
LEFTJOIN [User] AS U ON PM.User_Id = U.Id 
GROUPBY P.Id, P.Name

With your sample data the result would be:

ProjectID   ProjectName UserType0 (Name)    UserType0 (Register)    UserType1 (Name)
1           Project 1User123498374User22           Project 2NULLNULLNULL3           Project 3User64849888User54           Project 4User36546884User45           Project 5User784884446NULL6           Project 6NULLNULLUser87           Project 7NULLNULLNULL