I have a table which contains demographic information. Within this table I have the following columns
raceam_ind
raceasian
racebl__af_am
racehi_pacisl
racewhite
These columns will have either a Y or N value. It is possible for more than one column to have a Y value in the case of dual ethniticity. Each possible value maps to a numeric value. FOr example
1 = white
2 = African American/Black
3 = Asian
4 = Hawaiian or Pacific Islander
5 = American Indian
My issue is that I need the output to be race1,race2,race3,race4. So if I have a record with racebl_af_am set to Y and racewhite set to Y, then I need to output to be Race 1 = 2
Race 2 = 1
Race 3 = null
Race 4 = null
race 5 = null
Any thoughts on where to even start I thought about doing a case statement but it seems a little messy. Any thoughts or direction would be extremely helpful. Thanks
frank
ordering values
John Padilla
Hi Frank,
I don't know what happened to my previous posting, but it's not very readable. Let's give it another shot.
On Wed, 18 Jan 2006 10:10:47 -0800, <FJK@discussions.microsoft.com>
wrote:
(snip)
> Any thoughts on where to even start I thought about doing a case
>statement but it seems a little messy. Any thoughts or direction would
>be extremely helpful. Thanks
Hi Frank,
You could try something like this:
SELECT CASE WHEN racewhite = 'Y'
THEN 1
ELSE NULL
END AS Race1,
CASE WHEN racebl__af_am = 'Y'
THEN 2
ELSE NULL
END AS Race2,
(...)
FROM YourTable
If this doesn't answer your question, then please check the information
at www.aspfaq.com.5006 to find out what information you have to post to
enable us to help you.
--
Hugo Kornelis, SQL Server MVP
Draakje
In SQL 2005, I would:
1. UNPIVOT the row into a series of rows containing race, yes/no
2. use row_number() to number the rows, ordering by the response (y/n)
3. then I'd write some scalar logic to change the row_number() output to race 1..n and the outputs to 1/null from y/n.
Good luck!
Conor Cunningham
SQL Server Query Optimization Development Lead
Florin Lazar