Select DISTINCT items from multiple columns based upon match in another column

I hope the heading isn't too confusing.

I have a table that consists of 11 fields Field1, Field2, Field3, etc. I would like to have a query that would get the unique items/names from the table based upon a given criteria.

The data in the table consists of an organizational structure, so if a user were to select a supervisor in Field1 his/her subordinates would be listed in Fields2 - Fields11. A supervisor can be on multiple rows and have multiple subordinates.

Field1      Field2      Field3      Field4      Field5

James      Freda      Nancy            
Kirk       Jim      Lonie      Gretta      Marsha
Shelly      Bob      Mark            
James      Bill      Kelly      Todd      Pete


So, if a user wanted all the subordinates for James they would get a list of  Freda, Nancy, Bill, Kelly, Todd, Pete

I hope this makes sense?

This is what I tried but I'm not good at all on SQL statements.

SELECT DISTINCT Field1 from OrgStructure where 'James' IN (Field1) AND (Field2 IS NOTNULL) OR (Field3 IS NOTNULL) OR (Field4 IS NOTNULL) OR (Field5 IS NOTNULL) Order by Field1
LVL 8
ampapaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

amit_gCommented:
Try this ...

Select distinct Field2 from OrgStructure where Field1 = 'James'
Union
Select distinct Field3 from OrgStructure where Field1 = 'James'
Union
Select distinct Field4 from OrgStructure where Field1 = 'James'
Union
Select distinct Field5 from OrgStructure where Field1 = 'James'
0
curtis591Commented:
select distinct field2 from table where field1='James'
union all
select distinct field3 from table where field1='James'
union all
select distinct field4 from table where field1='James'
union all
select distinct field5 from table where field1='James'
0
curtis591Commented:
Looks like we posted that at the same time.
0
Brendt HessSenior DBACommented:
SELECT Distinct Field2
FROM OrgStructure
WHERE Field1 = 'James'
    AND Field2 Is Not Null

UNION ALL

SELECT Distinct Field3
FROM OrgStructure
WHERE Field1 = 'James'
    AND Field3 Is Not Null

UNION ALL

SELECT Distinct Field4
FROM OrgStructure
WHERE Field1 = 'James'
    AND Field4 Is Not Null

UNION ALL

SELECT Distinct Field5
FROM OrgStructure
WHERE Field1 = 'James'
    AND Field5 Is Not Null
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ampapaAuthor Commented:
Thank's for the extremely fast post as well as at the same time! All 3 answers work except for bhess1 also included the "IS NULL" criteria from my original attempt so I've got to go with his answer for completeness.

Thank's again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.