SQLTeam.com | Weblogs | Forums

Adding new column to query results


#1

I have table named Client with data in it that looks like this:
client.hispanic, client.white, client.asian, client.black, client.indian, client.hawaiian, client.other_race

When I query it, this is returned.

Latino	White	Asian	Black	Hawaiian Indian	Other
0	0	0	0	0	 1	0
1	0	0	0	0	 0	0
0	1	0	0	0	 0	0
0	0	0	1	0	 0	0
0	0	1	0	0	 0	0
0	0	0	0	0	 1	0
0	0	0	0	1	 0	0
1	0	0	0	0	 0	0
0	0	0	0	0	 0	1
0	0	0	0	1	 0	0
0	0	0	1	0	 0	1
0	1	0	0	0	 0	0
0	0	1	0	1	 0	0
99	0	0	0	0	 0	0

Where
Possible Responses for Latino equal
0 No
1 Yes
9 Not voluntarily reported
99 Unknown

AND

Possible responses for the rest equal
0 No
1 Yes
NULL Unknown

When I run this query, I would like to instead create a new column called Ethnicity just to display the actual names.

If 1, then display that column name. If there are multiple columns with 1, then display the first column name, followed by a comma and a space then the next column name.
If there are no answers of 1, then display Unknown.

What I would like to do is display human readable names like this.

Ethnicity
Indian
Latino
White
Black
Asian
Indian
Hawaiian
Latino
Other
Hawaiian
Black, Other
White
Asian, Hawaiian
Unknown

How might I do this?


#2
SELECT client.hispanic, client.white, client.asian, client.black, client.indian, client.hawaiian, client.other_race,
       STUFF(CASE WHEN client.hispanic = 1 THEN ', Latino' ELSE '' END
                + CASE WHEN client.white = 1 THEN ', White' ELSE '' END
                + CASE WHEN ...
             , 1, 2, '') AS [Ethnicity]
FROM [client]
WHERE ...

#3

Stuff!

That is wonderful, thank you so much!!