CODEWITHSUNDEEP
sqlsql-serverpivotpivot-tabledynamic-pivot
Federica
Federica

Reputation: 19

Pivot with undefined number of columns

I need to insert a pivot into a query. Each employee can belong to one or more groups and I would like to obtain as a result the various columns 'group 1', 'group 2', etc... with the name of the group they belong to. The employee/group association table (EmployeeGroup) consists of employee_id, group_id (1:n), the group table consists of id, name and the starting query is the following:

SELECT name, surname, ....
FROM select1

Table EMPLOYEE:
ID | SURNAME   | NAME
1    Jones       Oliver
2    Williams    Kathrine
3    Smith       Jennifer

Table GROUP:
ID | NAME  
1    Red
2    Green
3    White 

Table EMPLOYEE_GROUP:
ID | EMPLOYEE_ID | GROUP_ID
1    1             2
2    1             3
3    2             1
4    2             3
5    3             1
6    3             2
7    3             3

How can I modify this query by adding the part relating to the pivot to have the columns of the various groups? The result should be the following:

   NAME     |   SURNAME   |   GROUP 1  |  GROUP 2  | GROUP 3
   Kathrine     Williams      Red         White
   Oliver       Jones         Green       White
   Jennifer     Smith         Red         Green      White

This question is different from the following SQL Server dynamic PIVOT query? because I don't want a column headed with the group name for each group but, based on what I extract from the query, the columns/group will be as many as the maximum number of groups to which the employee belongs with the greater number of membership groups. In the example above, if Williams belonged to 3 groups, I would like 3 columns headed Group1, Group2, Group3 in total and each employee will have as values ​​of these columns the name/names of the group/groups they belong to or the empty.

Many thanks.

Upvotes: 0

Views: 41

Answers (0)

Related Questions