Smoothing Relational Data for Summary / Export

Question

Let's say we have two tables in MS Access db:

Service users:

| ID | Name | Other details... |
| 1  | Joe  | Blah...          |
| 2  | Fred | Qwerty...        |
| 3  | Bob  | Something else...|

Teams providing services:

| ID | TeamID | UserID |
| 1  | T1     | 1      |
| 2  | T2     | 1      |
| 3  | T2     | 2      |
| 4  | T3     | 2      |
| 5  | T3     | 3      |

I need to create a composite query that creates one row for each user, with the first few teams (by TeamID) assigning a seat in separate columns. How:

Query:

| UserID | Name | Team1 | Team2 |
| 1      | Joe  | T1    | T2    |
| 2      | Fred | T2    | T3    |
| 3      | Bob  | T3    | Null  |

I can get the Team1 column using max () from the subselect request, but I have a full mental block on how to reach Team2, Team3, etc. (Yes, I know that if more commands are assigned to a user than I create columns, the query will lose this information: this is not a problem).

: , ( 7). , , Null ( ). , , 7 .

2 - , ...:

...

SELECT UserTable.ID As UID, UserTable.Name, 
(SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID
ORDER BY TeamID) As Team1
FROM UserTable

... . ,...

SELECT UserTable.ID As UID, UserTable.Name, 
(SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID
ORDER BY TeamID) As Team1,
(SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID
AND TeamID <> Team1 ORDER BY TeamID) As Team2
FROM UserTable

... Team1. , // .. ?

Answer 1

-, 1 N , :

SELECT UserID, TeamID, 
       (SELECT count(*) FROM TeamTable t2 WHERE t2.TeamID <= TeamTable.TeamID and t2.UserID=TeamTable.UserID) AS position 
FROM TeamTable 
ORDER BY TeamID

TeamList. , 7 , :

SELECT UserTable.ID As UID, UserTable.Name, 
   (SELECT TeamID FROM TeamList WHERE UserTable.ID = TeamList.UserID AND position=1) As Team1,
   (SELECT TeamID FROM TeamList WHERE UserTable.ID = TeamList.UserID AND position=2) As Team2,
   [...]
FROM UserTable 

, TeamList . , TeamID. , TeamList, , , ( "= " ).

, , . .

Answer 2

- .

1: TeamUser

SELECT Teams.UserID, ServiceUsers.SName, Teams.TeamID
FROM ServiceUsers 
INNER JOIN Teams ON ServiceUsers.ID = Teams.UserID;

TRANSFORM First(TeamUser.TeamID) AS FirstOfTeamID
SELECT TeamUser.UserID, TeamUser.SName
FROM TeamUser
GROUP BY TeamUser.UserID, TeamUser.SName
PIVOT TeamUser.TeamID;

EDIT

.

TRANSFORM First(t.ATeamID) AS FirstOfATeamID
SELECT t.UserID, s.SName
FROM (SELECT Teams.UserID, First(Teams.ID) AS FirstOfID, 
             First(Teams.TeamID) AS ATeamID, "1st" As TeamCount
      FROM Teams
      GROUP BY Teams.UserID
      UNION 
      SELECT Teams.UserID, First(Teams.ID) AS FirstOfID, 
             First(Teams.TeamID) AS ATeamID, "2nd" As TeamCount
      FROM Teams
      WHERE ID Not In (SELECT First(Teams.ID) 
                       FROM Teams GROUP BY Teams.UserID)
      GROUP BY Teams.UserID) t
INNER JOIN ServiceUsers s ON t.UserID = s.ID
GROUP BY t.UserID, s.SName
PIVOT t.TeamCount

Answer 3

MS-Access, ? , Team1 0 , Team2 1 .. , MAX() . , ( , , ).

, ( Team1, Team2,...) , MAX() .

, MS-Access SQL, TOP. , . , . , , 3 , SELECT TOP(3) ..., SELECT TOP(2) ..., SELECT TOP(1)....

Answer 4

Team1 , Team1 , Team2

SELECT UserTable.ID As UID,UserTable.Name,T1.Team1,
(SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID AND TeamID <> T1.Team1 ORDER BY TeamID) As Team2 
FROM UserTable 
LEFT OUTER JOIN
    (
        SELECT UserTable.ID As UID,  
           (SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID 
        ORDER BY TeamID) As Team1, 
        FROM UserTable 
    )T1 ON UserTable.ID=T1.UID

CTE:

;With CTE_Team1 As (
        SELECT UserTable.ID As UID,  
           (SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID ORDER BY TeamID) As Team1, 
        FROM UserTable )
SELECT UserTable.ID As UID,UserTable.Name,T1.Team1,
   (SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID AND TeamID <> T1.Team1 ORDER BY TeamID) As Team2 
FROM UserTable 
LEFT OUTER JOIN
CTE_Team1 T1 ON UserTable.ID=T1.UID