SQL Query that can return intersecting data

Question

I have a hard time finding a good question title - let me just show you what I have and what the desired outcome is. I hope this can be done in SQL (I have SQL Server 2008).

1) I have a table called Contacts and in that table I have fields like these:

FirstName, LastName, CompanyName

2) Some demo data:

FirstName LastName CompanyName
John      Smith    Smith Corp
Paul      Wade  
Marc      Andrews  Microsoft
Bill      Gates    Microsoft
Steve     Gibbs    Smith Corp
Diane     Rowe     ABC Inc.

3) I want to get an intersecting list of people and companies, but companies only once. This would look like this:

Name
ABC Inc.
Bill Gates
Diane Rowe
John Smith   
Marc Andrews
Microsoft
Smith Corp
Steve Gibbs
Paul Wade

Can I do this with SQL? How?

Answer 1

I'm not sure what you mean by "intersecting," but you can easily get the results you describe as the union of two queries against that same table.

select
    t.firstname + ' ' + t.lastname
from
    mytable t
union
select
    t.company
from
    mytable t

Edit: UNION should make each SELECT distinct by default.

Answer 2

Does this do what you need?

SELECT FirstName + ' ' + LastName AS Name
FROM Contacts 
UNION
SELECT CompanyName
FROM Contacts 

(The UNION rather than UNION ALL will ensure distinctness of both top and bottom parts. mdma's answer will work if you do need the possibility of duplicate people names. You might need to add an ORDER BY Name depending on your needs)

Answer 3

You take all the person names, and then also add all the companies

SELECT CONCAT([First Name],' ',[Last Name]) AS Name FROM Contacts
UNION ALL
SELECT DISTINCT CompanyName FROM Contacts
   WHERE CompanyName IS NOT NULL

The DISTINCT keyword ensures that companies are output only once, and the WHERE clause removes rows where no company info is known.

If a person has the same name as a company, then this will output a duplicate. If you don't want that, then change UNION ALL to UNION, and any name will be output only once.