Populate a table from a query?

Question

question

GaryB52 asked Jun 29, '20

Populate a table from a query?

Hi Guys, I am really new to sql so i am sorry if this sounds stupid.

I have a query that shows me all the female drivers from two tables (employee and role)

SELECT role.idrole, employee.*, role.role FROM employee INNER JOIN role ON idrole=idemployee WHERE role.role="Driver" AND employee.gender="Female";

this query return 161 rows of female drivers.

now i need a table that will assign every 2 female drivers from this query to a team (example row 1 and row 2 are in team A row 3 and row 4 Team B etc)

any info or advice will be much appreciated.

sql sql-server

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-06-29T16:17:56Z

Kev Riley answered Jun 29, '20

If we assume there is a table of possible teams, lets call this Teams with an ID (starting at 1 and increasing by 1) and a Team name, then we can use multiple passes over the data with ranking functions to group together pairs of drivers

eg

TeamID      Team
----------- -------------
1           Team_A
2           Team_B
3           Team_C
4           Team_D
5           Team_E
6           Team_F
7           Team_G
8           Team_H
9           Team_I
10          Team_J
11          Team_K
12          Team_L
13          Team_M
14          Team_N

To start we use a row_number() and modulo functions to generate an unique id for each pair of drivers

select 
 role.idrole, employee.*, role.role, 
 row_number()over (order by idemployee)%2+row_number()over (order by idemployee) as grp
from employee 
inner JOIN role ON idrole=idemployee 
where role.role='Driver' AND employee.gender='Female'

which will give results like

idrole      idemployee  gender    role       other cols     grp
----------- ----------- --------- ---------  ...            -----
1           1           Female    Driver     ...            2
2           2           Female    Driver     ...            2
3           3           Female    Driver     ...            4
4           4           Female    Driver     ...            4
5           5           Female    Driver     ...            6
6           6           Female    Driver     ...            6
7           7           Female    Driver     ...            8
8           8           Female    Driver     ...            8
9           9           Female    Driver     ...            10
10          10          Female    Driver     ...            10
11          11          Female    Driver     ...            12
12          12          Female    Driver     ...            12
...

then we can use dense_rank() to make those groups monotonically identified

select 
 *, 
 dense_rank()over (order by grp) rankid
from
(
select 
 role.idrole, employee.*, role.role, 
 row_number()over (order by idemployee)%2+row_number()over (order by idemployee) as grp
from employee 
inner JOIN role ON idrole=idemployee 
where role.role='Driver' AND employee.gender='Female'
) employeegroupings

which gives

idrole      idemployee  gender    role       other cols     grp   rankid
----------- ----------- --------- ---------  ...            ----- --------
1           1           Female    Driver     ...            2     1
2           2           Female    Driver     ...            2     1
3           3           Female    Driver     ...            4     2
4           4           Female    Driver     ...            4     2
5           5           Female    Driver     ...            6     3
6           6           Female    Driver     ...            6     3
7           7           Female    Driver     ...            8     4
8           8           Female    Driver     ...            8     4
9           9           Female    Driver     ...            10    5
10          10          Female    Driver     ...            10    5
11          11          Female    Driver     ...            12    6
12          12          Female    Driver     ...            12    6
...

and that rankid can be used to join to the Teams table to get the team name

select
 *
from
(
select *, dense_rank()over (order by grp) rankid
from
(
select 
 role.idrole, employee.*, role.role, 
 row_number()over (order by idemployee)%2+row_number()over (order by idemployee) as grp
from employee 
inner JOIN role ON idrole=idemployee 
where role.role='Driver' AND employee.gender='Female'
) employeegroupings
) groupingsranked
join Teams on Teams.TeamID=groupingsranked.rankid

idrole      idemployee  gender    role       other cols     grp   rankid   TeamID      Team
----------- ----------- --------- ---------  ...            ----- -------- ----------- -----------
1           1           Female    Driver     ...            2     1        1           Team_A
2           2           Female    Driver     ...            2     1        1           Team_A
3           3           Female    Driver     ...            4     2        2           Team_B
4           4           Female    Driver     ...            4     2        2           Team_B
5           5           Female    Driver     ...            6     3        3           Team_C
6           6           Female    Driver     ...            6     3        3           Team_C
7           7           Female    Driver     ...            8     4        4           Team_D
8           8           Female    Driver     ...            8     4        4           Team_D
9           9           Female    Driver     ...            10    5        5           Team_E
10          10          Female    Driver     ...            10    5        5           Team_E
12          12          Female    Driver     ...            12    6        6           Team_F
11          11          Female    Driver     ...            12    6        6           Team_F
...

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question