Horizontal Self Table Join

Union tables join tables vertically, which is fine if you want one long set of data returned, but what if you want to compare data side-by-side in one table horizontally? And what if your are trying to conserve space by shifting up null cells? This is the kind of thing that is usually done with reports, or in Word or Excel. Yet if you want to save valuable editing time, how would you do that directly in SQL? I’m going to show you an uncommon table join. Uncommon, not because of the type of table join but because of the columns by which we are going to join.

The problem:
Let’s say you have a list of 23 employees that you have put on two different task force teams, each team having the same duties delegated according to the employee’s status level. You want to see the two teams listed side by side without redundant duties. That is, you want the employees from each team to appear horizontally next to each other wherever their duties are the same with nulls/blanks only appearing when one team does not have an employee with matching duty.

For instance:
This is team 500 (11 employees)

SELECT  * FROM employee WHERE team_id='500'

111	Al	Quinne	500	Manager
112	Betty	Green	500	Specialist
113	James	Saintjames	500	Staff
114	Jane	Hathaway	500	Manager
126	Peter	Sangabriel	500	Specialist
127	Gabriella	Wintruabe	500	Specialist
128	Bartholomew	Jacoby	500	Specialist
129	Micha	Elcondios	500	Programmer
130	Jack	Delta	500	Programmer
131	Ian	Shrewinicki	500	Programmer
132	Lena	Lawrence	500	Consultant

This is team 501 (12 employees)

SELECT * FROM employee WHERE team_id='501'

115	Ethan	Walker	501	Specialist
116	Wendy	Wright	501	Consultant
117	Perry	Lsughton	501	Programmer
118	Mary	Truce	501	Staff
119	Caitlin	Lancer	501	Specialist
120	Tobias	Indigo	501	Specialist
121	Oscar	Funkmier	501	Specialist
122	Quincy	Openhiem	501	Specialist
123	Judy	Trudy	501	Specialist
124	Pfeiffer	Jones	501	Consultant
125	Deanna	Kincade	501	Consultant
133	Bob	Urunkle	501	Manager

This is the result that you want
(15 overlapping records, side-by-side – horizontal)

Team 500	duty_name	Team 501
Lena Lawrence	Consultant	Wendy Wright
	Consultant	Pfeiffer Jones
	Consultant	Deanna Kincade
Jane Hathaway	Manager	Bob Urunkle
Al Quinne	Manager
Jack Delta	Programmer
Ian Shrewinicki	Programmer
Micha Elcondios	Programmer	Perry Lsughton
Betty Green	Specialist	Caitlin Lancer
Peter Sangabriel	Specialist	Tobias Indigo
Gabriella Wintruabe	Specialist	Oscar Funkmier
Bartholomew Jacoby	Specialist	Quincy Openhiem
	Specialist	Judy Trudy
	Specialist	Ethan Walker
James Saintjames	Staff	Mary Truce

The Solution
The key is to create a rank for each duty in each of the two tables and then join the select statements on the matching ranks for each duty. Let’s take it step by step.

Create a rank:

SELECT row_number() OVER ( PARTITION BY duty_name ORDER BY duty_name ) AS DutyRank,
 * FROM employee WHERE team_id='500'

DutyRank	emp_id	emp_first_name	emp_last_name	team_id	duty_name
1	132	Lena	Lawrence	500	Consultant
1	114	Jane	Hathaway	500	Manager
2	111	Al	Quinne	500	Manager
1	129	Micha	Elcondios	500	Programmer
2	130	Jack	Delta	500	Programmer
3	131	Ian	Shrewinicki	500	Programmer
1	112	Betty	Green	500	Specialist
2	126	Peter	Sangabriel	500	Specialist
3	127	Gabriella	Wintruabe	500	Specialist
4	128	Bartholomew	Jacoby	500	Specialist
1	113	James	Saintjames	500	Staff

SELECT row_number() OVER ( PARTITION BY duty_name ORDER BY duty_name ) AS DutyRank,
 * FROM employee WHERE team_id='501'

DutyRank	emp_id	emp_first_name	emp_last_name	team_id	duty_name
1	116	Wendy	Wright	501	Consultant
2	124	Pfeiffer	Jones	501	Consultant
3	125	Deanna	Kincade	501	Consultant
1	133	Bob	Urunkle	501	Manager
1	117	Perry	Lsughton	501	Programmer
1	119	Caitlin	Lancer	501	Specialist
2	120	Tobias	Indigo	501	Specialist
3	121	Oscar	Funkmier	501	Specialist
4	122	Quincy	Openhiem	501	Specialist
5	123	Judy	Trudy	501	Specialist
6	115	Ethan	Walker	501	Specialist
1	118	Mary	Truce	501	Staff

Now you can join the two tables via sub-query on their duty and duty rank:

SELECT T500.emp_first_name +' '+ T500.emp_last_name AS 'Team 500',
 T501.emp_first_name +' '+ T501.emp_last_name AS 'Team 501'
 FROM
(SELECT row_number() OVER ( PARTITION BY duty_name ORDER BY duty_name ) AS DutyRank, *
 FROM employee WHERE team_id='500') T500

FULL OUTER JOIN
 (SELECT row_number() OVER ( PARTITION BY duty_name ORDER BY duty_name ) AS DutyRank, *
 FROM employee WHERE team_id='501') T501
 ON T500.duty_name=T501.duty_name AND T500.DutyRank=T501.DutyRank

Team 500	Team 501
Lena Lawrence	Wendy Wright
NULL	Pfeiffer Jones
NULL	Deanna Kincade
Jane Hathaway	Bob Urunkle
Al Quinne	NULL
Micha Elcondios	Perry Lsughton
Jack Delta	NULL
Ian Shrewinicki	NULL
Betty Green	Caitlin Lancer
Peter Sangabriel	Tobias Indigo
Gabriella Wintruabe	Oscar Funkmier
Bartholomew Jacoby	Quincy Openhiem
NULL	Judy Trudy
NULL	Ethan Walker
James Saintjames	Mary Truce

The last thing we need to do is add a column of distinct duty. I also added ISNULL to handle nulls as blanks. Here’s the final code:

SELECT ISNULL(T500.emp_first_name, '') +' '+ ISNULL(T500.emp_last_name, '') AS 'Team 500',
 D.duty_name, ISNULL(T501.emp_first_name,'') +' '+ ISNULL(T501.emp_last_name,'') AS 'Team 501'
 FROM
(SELECT row_number() OVER ( PARTITION BY duty_name ORDER BY duty_name ) AS DutyRank, *
 FROM employee WHERE team_id='500') T500

FULL OUTER JOIN
 (SELECT row_number() OVER ( PARTITION BY duty_name ORDER BY duty_name ) AS DutyRank, *
 FROM employee WHERE team_id='501') T501
 ON T500.duty_name=T501.duty_name AND T500.DutyRank=T501.DutyRank

INNER JOIN
 (SELECT distinct duty_name FROM employee) D
 ON D.duty_name=T500.duty_name OR D.duty_name=T501.duty_name
 ORDER BY D.duty_name

Team 500	duty_name	Team 501
Lena Lawrence	Consultant	Wendy Wright
	Consultant	Pfeiffer Jones
	Consultant	Deanna Kincade
Jane Hathaway	Manager	Bob Urunkle
Al Quinne	Manager
Jack Delta	Programmer
Ian Shrewinicki	Programmer
Micha Elcondios	Programmer	Perry Lsughton
Betty Green	Specialist	Caitlin Lancer
Peter Sangabriel	Specialist	Tobias Indigo
Gabriella Wintruabe	Specialist	Oscar Funkmier
Bartholomew Jacoby	Specialist	Quincy Openhiem
	Specialist	Judy Trudy
	Specialist	Ethan Walker
James Saintjames	Staff	Mary Truce

Background/Supporting Code:
If you want to test this code yourself, here’s the sql to create the table and data:
(disclaimer: These are completely fabricated names. Any similarity to real persons is unintentional.)

create table employee
 (emp_id char(3) not null,
 emp_first_name varchar(20) not null,
 emp_last_name varchar(20) not null,
 team_id char(3) not null,
 duty_name varchar(20))

--intitial records
 insert into employee
 values ('111','Al','Quinne', '500', 'Manager'),
 ('112','Betty','Green', '500', 'Specialist'),
 ('113','James','Saintjames', '500', 'Staff'),
 ('114','Jane','Hathaway', '500', 'Manager'),
 ('115','Ethan','Walker', '501', 'Specialist'),
 ('116','Wendy','Wright', '501', 'Consultant'),
 ('117','Perry','Lsughton', '501', 'Programmer'),
 ('118','Mary','Truce', '501', 'Staff'),
 ('119','Caitlin','Lancer', '501', 'Specialist'),
 ('120','Tobias','Indigo', '501', 'Specialist'),
 ('121','Oscar','Funkmier', '501', 'Specialist'),
 ('122','Quincy','Openhiem', '501', 'Specialist'),
 ('123','Judy','Trudy', '501', 'Specialist'),
 ('124','Pfeiffer','Jones', '501', 'Consultant'),
 ('125','Deanna','Kincade', '501', 'Consultant'),
 ('126','Peter','Sangabriel', '500', 'Specialist'),
 ('127','Gabriella','Wintruabe', '500', 'Specialist'),
 ('128','Bartholomew','Jacoby', '500', 'Specialist'),
 ('129','Micha','Elcondios', '500', 'Programmer'),
 ('130','Jack','Delta', '500', 'Programmer'),
 ('131','Ian','Shrewinicki', '500', 'Programmer'),
 ('132','Lena','Lawrence', '500', 'Consultant'),
 ('133','Bob','Urunkle', '501', 'Manager')