List box of 5 fields in one record

Hi experts,

I have a table that contains five fields.  Each field is a category.  The categories in each field can change for each record in the table.

Lets say that I have the following in my table:

ID     Name      Cat1        Cat2           Cat3       Cat4           Cat5
1       Amy     Finance     Marketing    Sales         IT            Admin
2      Gerald  Marketing  Finance       <Blank>   Admin      <Blank>
3       Terry   Finance      <Blank>      <Blank>  <Blank>   <Blank>  


In the list box, for Amy, I would like to see:

Finance
Marketing
Sales
IT
Admin


In the list box for Gerald I would like to see:

Marketing
Finance
Admin

and for Terry I would like to see in the list box:

Finance.

I need the list box to recognise that there will only ever be 5 items in the list because there are only 5 categories. I also need the list box to know that the top item is always category 1, second is always category 2 etc...

Can anybody help with the SQL as I am stuck on where to start on this one.

Any help is always appreciated.
Regards
Terry
TerenceHewettAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
OK.. This one should help you out
SELECT Category from (
SELECT ID,Cat1 as Category,1 as order_field FROM urtable
UNION
SELECT ID,Cat2,2 FROM urtable
UNION
SELECT ID,Cat3,3 FROM urtable
UNION
SELECT ID,Cat4,4 FROM urtable
UNION
SELECT ID,Cat5,5 FROM urtable ) as temp
WHERE Category IS NOT NULL
AND ID = 1
ORDER BY order_field

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try out this query:

Revert out with issues specifying the expected result set so that I can help you out further
SELECT Category from (
SELECT Cat1 as Category FROM urtable
UNION
SELECT Cat2 FROM urtable
UNION
SELECT Cat3 FROM urtable
UNION
SELECT Cat4 FROM urtable
UNION
SELECT Cat5 FROM urtable ) as temp
WHERE Category IS NOT NULL

Open in new window

0
 
TerenceHewettAuthor Commented:
Hi rrjegan17,

SELECT temp.Category AS Expr1
FROM [SELECT Category1 as Category FROM tblDetails
UNION
SELECT Category2 FROM tblDetails
UNION
SELECT Category3 FROM tblDetails
UNION
SELECT Category4 FROM tblDetails
UNION
SELECT Category5 FROM tblDetails ]. AS temp
WHERE (((temp.Category) Is Not Null));

I think this will work, but how would I limit this list to the items per record ID? The record ID is simply "ID" and is an autonumber.  To clarify, I am looking to show only those categories in the list box that relate to the ID criteria.

Thank you again.

Terry
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Missed that one and this will do for ID= 1:
SELECT Category from (
SELECT Cat1 as Category FROM urtable
UNION
SELECT Cat2 FROM urtable
UNION
SELECT Cat3 FROM urtable
UNION
SELECT Cat4 FROM urtable
UNION
SELECT Cat5 FROM urtable ) as temp
WHERE Category IS NOT NULL
AND ID = 1

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
A small mistake and Ignore the previous one:
SELECT Category from (
SELECT ID,Cat1 as Category FROM urtable
UNION
SELECT ID,Cat2 FROM urtable
UNION
SELECT ID,Cat3 FROM urtable
UNION
SELECT ID,Cat4 FROM urtable
UNION
SELECT ID,Cat5 FROM urtable ) as temp
WHERE Category IS NOT NULL
AND ID = 1
 
Or for Better Performance
 
SELECT Category from (
SELECT ID,Cat1 as Category FROM urtable
WHERE ID = 1
UNION
SELECT ID,Cat2 FROM urtable
WHERE ID = 1
UNION
SELECT ID,Cat3 FROM urtable
WHERE ID = 1
UNION
SELECT ID,Cat4 FROM urtable
WHERE ID = 1
UNION
SELECT ID,Cat5 FROM urtable
WHERE ID = 1 ) as temp
WHERE Category IS NOT NULL

Open in new window

0
 
TerenceHewettAuthor Commented:
Hi rrjegan17,

Your last post does work but I have a slight issue.  In my table I have

Cat1
Cat2
Cat3
Cat4
Cat5

I need the list box to always be displayed in this manner.  For example, I have a record that has a 6 in Cat1 and a 1 in Cat2.  When the list box displays the results, it is displaying it in ascending order (i.e. 1, 6).  I need the list box to always display the information in field order (i.e. Cat1, Cat2, Cat3 etc.).

Any ideas on how I would achieve this.  Your help on this has been invaluable.

Regards
Terry
0
 
TerenceHewettAuthor Commented:
This worked perfectly well.  I am happy to start a new Q if required, but the Cat1 to 5 fields are stored as numbers.  The numbers relate to another table.  The other table, called tblLookupCat has an ID field and a Description field.  

The ID field matches the same number as in tblDetails and the Description field shows what that number means.

For example, I currently have 2 records displayed in my list box.  A 6 and a 1.

The 6 is a representation of Admin
The 1 is a representation of Marketing.  

In the list box, although the ID's are correct, they mean nothing to the user.  Is there anyway that I can add tblLookupCat to the SQL you have created and link the ID's together so that I can display the description in the list box also?

Thank you very much again and again.

Terry
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope Joining with a new table will not be a concern.

If you specify the other table structure, I can help you out in the join too.
0
 
TerenceHewettAuthor Commented:
Thanks - will do that now.
0
 
TerenceHewettAuthor Commented:
No worries - I have been able to join the table with no problems. I tried this earlier and got an error.  working now, so no worries.

Thank you very much for all your help. You have saved my bacon - and lots of time trying to create the SQL myself.
Regards
Terry
0
 
TerenceHewettAuthor Commented:
many many thanks - your help has been very much appreciated.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Glad to help you out Terry
0
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.

All Courses

From novice to tech pro — start learning today.