I have the following structure on SQLFiddle that has been created by others who helped me (Using two columns in a PIVOT):
CREATE TABLE Project (
Id int,
Name VARCHAR(50),
CONSTRAINT PK_Project PRIMARY KEY (Id));
CREATE TABLE [User] (
Id int,
Name VARCHAR(50),
Register int,
CONSTRAINT PK_User PRIMARY KEY (Id));
CREATE TABLE ProjectMember (
Project_Id int,
User_Id int,
MemberType tinyint,
CONSTRAINT PK_ProjectMember PRIMARY KEY(Project_Id, User_Id),
CONSTRAINT FK_ProjectMember_Project FOREIGN KEY(Project_Id) REFERENCES Project(Id),
CONSTRAINT FK_ProjectMember_User FOREIGN KEY(User_Id) REFERENCES [User](Id));
INSERT INTO Project(Id, Name) VALUES(1, 'Project 1');
INSERT INTO Project(Id, Name) VALUES(2, 'Project 2');
INSERT INTO Project(Id, Name) VALUES(3, 'Project 3');
INSERT INTO Project(Id, Name) VALUES(4, 'Project 4');
INSERT INTO Project(Id, Name) VALUES(5, 'Project 5');
INSERT INTO Project(Id, Name) VALUES(6, 'Project 6');
INSERT INTO Project(Id, Name) VALUES(7, 'Project 7');
INSERT INTO [User](Id, Name, Register) VALUES(1, 'User 1', 23498374);
INSERT INTO [User](Id, Name, Register) VALUES(2, 'User 2', 96849887);
INSERT INTO [User](Id, Name, Register) VALUES(3, 'User 3', 6546884);
INSERT INTO [User](Id, Name, Register) VALUES(4, 'User 4', 8489848);
INSERT INTO [User](Id, Name, Register) VALUES(5, 'User 5', 4684854);
INSERT INTO [User](Id, Name, Register) VALUES(6, 'User 6', 4849888);
INSERT INTO [User](Id, Name, Register) VALUES(7, 'User 7', 84884446);
INSERT INTO [User](Id, Name, Register) VALUES(8, 'User 8', 77554454);
INSERT INTO [User](Id, Name, Register) VALUES(9, 'User 9', 77853997);
INSERT INTO ProjectMember(Project_Id, User_Id, MemberType) VALUES(1, 1, 0);
INSERT INTO ProjectMember(Project_Id, User_Id, MemberType) VALUES(1, 2, 1);
INSERT INTO ProjectMember(Project_Id, User_Id, MemberType) VALUES(1, 3, 2);
INSERT INTO ProjectMember(Project_Id, User_Id, MemberType) VALUES(1, 4, 2);
INSERT INTO ProjectMember(Project_Id, User_Id, MemberType) VALUES(1, 5, 2);
INSERT INTO ProjectMember(Project_Id, User_Id, MemberType) VALUES(3, 6, 0);
INSERT INTO ProjectMember(Project_Id, User_Id, MemberType) VALUES(3, 5, 1);
INSERT INTO ProjectMember(Project_Id, User_Id, MemberType) VALUES(4, 3, 0);
INSERT INTO ProjectMember(Project_Id, User_Id, MemberType) VALUES(4, 4, 1);
INSERT INTO ProjectMember(Project_Id, User_Id, MemberType) VALUES(4, 5, 2);
INSERT INTO ProjectMember(Project_Id, User_Id, MemberType) VALUES(5, 7, 0);
INSERT INTO ProjectMember(Project_Id, User_Id, MemberType) VALUES(6, 8, 1);
I have a new requirement from my client. He needs a filter to return all projects that have a MemberType = 2 and a specific User.Id, but the returned values doesn't need to include these values.
Example:
With this SQL statement:
SELECT
ProjectID = P.Id,
ProjectName = P.Name,
[UserType0 (Name)] = MAX(CASE WHEN MemberType = 0 THEN u.Name END),
[UserType0 (Register)] = MAX(CASE WHEN MemberType = 0 THEN Register END),
[UserType1 (Name)] = MAX(CASE WHEN MemberType = 1 THEN u.Name END),
(CASE WHEN MemberType = 2 THEN u.Name END) as [UserType2]
FROM Project AS P
LEFT JOIN ProjectMember AS PM ON P.Id = PM.Project_Id
LEFT JOIN [User] AS U ON PM.User_Id = U.Id
GROUP BY P.Id, P.Name, PM.MemberType, U.Name
I get this value:
| ProjectID | ProjectName | UserType0 (Name) | UserType0 (Register) | UserType1 (Name) | UserType2 |
|-----------|-------------|------------------|----------------------|------------------|-----------|
| 1 | Project 1 | User 1 | 23498374 | (null) | (null) |
| 1 | Project 1 | (null) | (null) | User 2 | (null) |
| 1 | Project 1 | (null) | (null) | (null) | User 3 |
| 1 | Project 1 | (null) | (null) | (null) | User 4 |
| 1 | Project 1 | (null) | (null) | (null) | User 5 |
| 2 | Project 2 | (null) | (null) | (null) | (null) |
| 3 | Project 3 | User 6 | 4849888 | (null) | (null) |
| 3 | Project 3 | (null) | (null) | User 5 | (null) |
| 4 | Project 4 | User 3 | 6546884 | (null) | (null) |
| 4 | Project 4 | (null) | (null) | User 4 | (null) |
| 4 | Project 4 | (null) | (null) | (null) | User 5 |
| 5 | Project 5 | User 7 | 84884446 | (null) | (null) |
| 6 | Project 6 | (null) | (null) | User 8 | (null) |
| 7 | Project 7 | (null) | (null) | (null) | (null) |
But what I need is to return a single row per project that contains a user with a specific ID and MemberType = 2. How can I add this filter without changing the results like specified on Using two columns in a PIVOT?
