I have a table that has just raw data: A contact in one column, and an option they chose in another, like so:
Contact Option
A 1
A 3
B 1
C 2
D 3
E 2
E 3
TableOfOptions
1
2
3
4
And what I want is to return a result set that has a contact for each option, regardless of whether it was chosen. Basically, I want to "fill out" the result set, with NULLs for options not chosen. So there are two tables, one with the Contact to Option Choice mapping, and one with a list o fall the options. The desired result set looks like this.
Contact Option Chosen
A 1 1
A 2 NULL
A 3 3
A 4 NULL
B 1 1
B 2 NULL
B 3 NULL
B 4 NULL
C 1 NULL
C 2 2
C 3 NULL
C 4 NULL
Etc, etc, I'm sure you get the point. I'm can't figure out how to project out every row in the 'options' table for each contact in the "ContactOptions" table, filling out the missing rows with NULLs for the OptionChosen. Every "contact" will have at least one choice of option, some may have several, some may have all. So I need to "add" between 0 and "n-1" options to each Contact row, with all the "added" rows being NULL.
My first though was a CROSS JOIN and have a CASE WHEN x.Option = y.Option then x.Option else NULL END for the third column... but I'm finding that in some cases I'm getting duplicate rows, and I can't figure out why.
Note my real world example is slightly more complicated in that I want to carry many more properties than just one (i.e Contact A has four other properties I want repeated with each option, but I figured that didn't have much to do with the solution). But the combination of "Contact + Option" should be unique in the result set.
My end goal is to be able to query for a given option, but always get ALL contacts. For example, if I want to see "How did people choose option 2", I want A through E to each have ONE row in the result set, with either a value or a NULL based on whether they chose the option or not.
EDIT: Here's sample code that I've tried, but which results in lots of duplicate rows I don't want:
CREATE TABLE ContactOption ([Contact] CHAR(1), [Option] int);
INSERT INTO ContactOption ([Contact], [Option])
VALUES
('A', 1),
('A', 3),
('B', 1),
('C', 2),
('D', 3),
('E', 2),
('E', 3);
CREATE TABLE TableOfOptions ([Option] int);
INSERT INTO TableOfOptions ([Option])
VALUES
(1),
(2),
(3),
(4);
SELECT co.[Contact],
t.[Option],
CASE WHEN co.[Option] = t.[Option] THEN co.[Option] ELSE NULL END AS [Choice]
FROM ContactOption co
CROSS JOIN TableOfOptions t
ORDER BY co.[Contact], t.[Option]