Using two columns in a PIVOT

Question

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 Register column that contains the user's ID in the company's HR department. I need to return this Register column when a user has MemberType = 0. How can I do this?

Here is a script to create the sample database:

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 to return the data like this:

ProjectId | ProjectName | UserType0 (Name) | UserType0 (Register) | UserType1 (Name)

jpw · Accepted Answer · 2015-07-31 12:42:57Z

2

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(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) 
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

With your sample data the result would be:

ProjectID   ProjectName UserType0 (Name)    UserType0 (Register)    UserType1 (Name)
1           Project 1   User 1              23498374                User 2
2           Project 2   NULL                NULL                    NULL
3           Project 3   User 6              4849888                 User 5
4           Project 4   User 3              6546884                 User 4
5           Project 5   User 7              84884446                NULL
6           Project 6   NULL                NULL                    User 8
7           Project 7   NULL                NULL                    NULL

answered Jul 31, 2015 at 12:42

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

juliano.net Over a year ago

Just out of curiosity. Do you mean this approach is better or there is no way to achieve the same result using pivot operator?

jpw Over a year ago

@JulianoNunesSilvaOliveira It's probably possible using pivot but I think it would involve using aggregate functions in the end anyway, and to me the approach I used feels easier. That's just my opinion of course.

Collectives™ on Stack Overflow

Using two columns in a PIVOT

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related