asked on
TSQL function to retrieve multiple values
Hello,
Uh oh! SQL guy is on vacation again!
I'm trying to feed a "Race" field on an RDLC report. My database has a person table with bit fields to indicate the race(s) a person belongs to.
The layout is something like this:
person_ID | name | DOB | IsAsian | IsBlack | IsNativeHawaiian | IsAlaskaNative | IsWhite
A person can belong to more than one race group. I'm trying to write a function that would output a string of each race the person belongs to. For example, if IsAlaskaNative='1' AND IsWhite='1' the output would be "Alaskan Native, White".
Any help you can provide will be greatly appreciated.
Thanks!
Uh oh! SQL guy is on vacation again!
I'm trying to feed a "Race" field on an RDLC report. My database has a person table with bit fields to indicate the race(s) a person belongs to.
The layout is something like this:
person_ID | name | DOB | IsAsian | IsBlack | IsNativeHawaiian | IsAlaskaNative | IsWhite
A person can belong to more than one race group. I'm trying to write a function that would output a string of each race the person belongs to. For example, if IsAlaskaNative='1' AND IsWhite='1' the output would be "Alaskan Native, White".
Any help you can provide will be greatly appreciated.
Thanks!
Microsoft SQL ServerMicrosoft SQL Server 2008
Last Comment
ttist25
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
This is the craftsmanship of coding. :) Different ways to get the same result.
Thanks so much for all of your help!
Thanks so much for all of your help!
CASE WHEN raceBits & 1 > 0 THEN ', Asian' ELSE '' END +
CASE WHEN raceBits & 2 > 0 THEN ', Black' ELSE '' END +
CASE WHEN raceBits & 4 > 0 THEN ', Alaskan Native' ELSE '' END +
CASE WHEN raceBits & 8 > 0 THEN ', White' ELSE '' END,
1, 2, '') AS Race