title: SQL Query to group multiple items with the same index into the same row
body: <p>So I have three tables containing data about users and their departments, and I need to create a query that organizes the data between them. The first table contains the users' names and their IDs, and looks like this:</p>
  
  <pre><code>user_name   user_id
  -----------------------
  bob         1
  chuck       2
  bill        3
  </code></pre>
  
  <p>The next table indexes the users with their department IDs, like this:</p>
  
  <pre><code>user_id     department_id
  -----------------------------
  1           x
  1           y
  2           x
  3           z
  2           z
  2           y
  </code></pre>
  
  <p>and the last table lists the department names according to their IDs:</p>
  
  <pre><code>department_id   department_name
  -------------------------------
  x               research
  y               development
  z               advertising
  </code></pre>
  
  <p>How do I write a query that lists all the users along with each department they are in? It should look something like this:</p>
  
  <pre><code>user_name   user_id     department_1    department_2    department_3
  ----------------------------------------------------------------------------
  bob         1           research        development
  chuck       2           research        development     advertising
  bill        3           advertising
  </code></pre>
  
  <p>I have tried many different methods and I can't find anything that gives me this result. My output currently lists duplicate users if they have more than one department, like this:</p>
  
  <pre><code>user_name   user_id     department_1    department_2    department_3
  ----------------------------------------------------------------------------
  bob         1           research        research        research
  bob         1           development     development     development
  chuck       2           research        research        research
  chuck       2           development     development     development
  chuck       3           advertising     advertising     advertising
  bill        3           advertising     advertising     advertising
  </code></pre>
  
  <p>Current code:</p>
  
  <pre><code>WITH DATA AS
  (
  SELECT 
  Users.user_name AS Name,
  Users.user_id AS Id,
  Departments.department_name AS Department1,
  Departments.department_name AS Department2,
  Departments.department_name AS Department3
  FROM Users
      JOIN Department_Index ON Users.user_id = Department_Index.user_id
      JOIN Departments ON Departments.department_id = Department_Index.department_id
  WHERE Departments.department_id = Department_Index.department_id
  )
  
  SELECT
  Name,Id,Department1,Department2,Department3
  FROM DATA
  </code></pre>
  
  <p>I also tried something like this</p>
  
  <pre><code>WITH DATA AS
  (
  SELECT 
  Users.user_name AS Name,
  Users.user_id AS Id,
  ( SELECT Departments.department_name
    FROM Departments
       JOIN Department_Index ON Department_Index.user_id = Users.user_id
    WHERE Departments.department_name = Department_Index.department_name
  ) AS Departments
  FROM Users
  )
  
  SELECT
  Name,Id,Departments[0],Departments[1],Departments[2]
  FROM DATA
  </code></pre>
  
  <p>But that didn't work either.</p>
  
  <p>Any help is appreciated!</p>